Optimizing mysql slow query

Question

below query taking more than 2.5 seconds running from 20lacs records;

SELECT lid,nid ,sum(nview) as view_cnt 
FROM nv 
WHERE  lid = 1 
GROUP BY nid
ORDER BY view_cnt desc 
LIMIT 20;

i updated the possible key as indexes, Please help me to optimize this query

id  select_type table   type    possible_keys    key        key_len ref      rows    Extra
1   SIMPLE      nv      ref     lid              lid        4       const    406282  "Using where; Using temporary; Using filesort"

run SHOW CREATE TABLE view_cnt and EXPLAIN SELECT ..(yourquery).. and include the results in your question. — Kaii
Your ORDER BY clause also seems suspicious, you probably want to ORDER BY view_cnt instead? — Romain
How many different nid are there for a lid on average? The ORDER BY clause requires the filesort operation, which is super-slow, if you have too many rows to sort. — Romain

piotrm piotrm · Accepted Answer · 2012-04-26T14:52:30

You can try adding an index on all columns used in the query:

ALTER TABLE nv ADD INDEX lnv(lid,nid,nview)

so that mysql uses column values from the index instead of using index only for the row lookups. You should see additional using index in your explain extra column. It can probably speed up your query a bit, but don't expect a lot, filesort in this case is unavoidable without changing the table schema.

If you decide to use this index you can safely remove index on single lid column as the composite one can be used for lid lookups as well.

Optimizing mysql slow query

2 Answers