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.