database optimization question
Posted: Wed Aug 24, 2005 1:06 am
I'm designing an application that will query the database many times in short bursts. I want it to be able to perform many queries in as short a time as possible, say 10,000 plus at a time. I always use explain on my queries to find ways to improve them. According to the mysql documentation, if speed is your issue, you do not want any queries that need have "using temporary, using filesort" in the extra column of an explain result. These are often times used when you gotta use the GROUP BY clause, something I saw often in my code when finding out how many child records there were of a parent related by the parents primary key as an index on the child similarly named field.
For example, consider this query:
an explain of this query will give you this
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE child_table index parent_id parent_id 4 NULL 2 Using index; Using temporary; Using filesort
1 SIMPLE parent_table eq_ref PRIMARY PRIMARY 4 database.child_table.parent_id 1 Using index
I got the "using temporary and using filesort", which i dont want as this query will be run quiet often. My solution was to create a field "child_count" on the parent table, which was updated whenever a child with the same parent_id was created, moved, or deleted. Of course this was all done in php, and while the code is pretty good, the fact that this updating is not done (or simply queried using count() like above) in mysql cuts down on referential integrity.
Now here are my questions:
Am i getting too picky about optimization?
Am I going about optimizing this query the right way?
Thanks for the help.
For example, consider this query:
Code: Select all
SELECT count(child.parent_id)
FROM parent_table, child_table
WHERE child_table.parent_id = parent_table.parent_id
GROUP BY parent_table.parent_idid select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE child_table index parent_id parent_id 4 NULL 2 Using index; Using temporary; Using filesort
1 SIMPLE parent_table eq_ref PRIMARY PRIMARY 4 database.child_table.parent_id 1 Using index
I got the "using temporary and using filesort", which i dont want as this query will be run quiet often. My solution was to create a field "child_count" on the parent table, which was updated whenever a child with the same parent_id was created, moved, or deleted. Of course this was all done in php, and while the code is pretty good, the fact that this updating is not done (or simply queried using count() like above) in mysql cuts down on referential integrity.
Now here are my questions:
Am i getting too picky about optimization?
Am I going about optimizing this query the right way?
Thanks for the help.