CODEWITHSUNDEEP
mysqloptimization
sk2
sk2

Reputation: 1181

Optimizing mysql slow query

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"

Upvotes: 0

Views: 1450

Answers (2)

piotrm
piotrm

Reputation: 12366

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.

Upvotes: 1

Bharath
Bharath

Reputation: 166

You have indexed only lid column, can you add index to nid column and verify the query execution time

Upvotes: 0

Related Questions