Mysql Query Optimization 12.07.08
I need to optimize one mysql SELECT query. The query is a join of two tables:
SELECT count(a.unique_id) as cnt, a.category_id, b.category_name from entries as a, categories as b WHERE a.category_id=b.category_id GROUP BY b.category_id ORDER BY b.category_name asc;
All id fields are indexed in both tables.
The query takes 6 seconds. The query’s two intrinsic selects respond in under 0.5 seconds each. So, the single select:
SELECT count(*) as cnt, category_id from entries GROUP BY category_id ;
completes in under 0.5 seconds.
I need the query optimized using, better UNION or JOIN specification in mysql, to reduce the query time below a second.
