…Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without …MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL gets data in already sorted order…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY …group by so for large number of rows you’ve simply got to use different solutions, such as summary table. With MySQL…avoid joins as much as possible. ORDER BY I already wrote about ORDER BY… back to use filesort, and it does…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
…GROUP BY and an ORDER BY clause? Right; you’re stuck, unless your query groups by the same column as its sort column. You can optimize by…filesort” in the EXPLAIN report. Hopefully, the size of this interim result set is relatively small, after the GROUP BY…by counting…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…MySQL time and that’s how mk-query-digest groups and orders data by… avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 519 # Exec …0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full…nested group-by and order-by within a group so I would avoid the…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… can use. Just remember avoiding accessing database is the… by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql…are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If there is no proper index and query has to use “filesort…
