This query run in 30s. How can I optimize it?

As a follow up on my previous question here: Link

These are my tables:

-----------------------------------
ID | ChapterNo | HitCount |  MID
-----------------------------------
1  |    2      |   1000   |   1
2  |    2      |   2000   |   1
3  |    1      |   3000   |   1
4  |    3      |   1000   |   1
5  |    1      |   3500   |   1
-----------------------------------

to archive the result, i tried to use the ff query:

SELECT t1.id, t1.hitcount, t1.chapterno
    FROM chapter as t1
    WHERE t1.hitcount = (select max(hitcount) from chapter where chapterno = t1.chapterno and `mid` = t1.`mid`)
    AND t1.`mid` = '2524'
    ORDER BY t1.chapterno DESC

ID | ChapterNo | HitCount |  
---------------------------
4  |    3      |   1000   |
2  |    2      |   2000   |
5  |    1      |   3500   |
---------------------------

This query seem to works really fine at first, but in scale, after I import 80,000 records for testing and implementing. I find out that this ran for 30second. A explain shows:

sel_type  table      type   posible_key  key       keyLen   ref        rows        Extra
PRIMARY   t1         ref    mid_idx      mid_idx    8       const      *3289*    Using where; Using filesort
PRIMARY   chapter    ref    mid_idx      mid_idx    8       m.t1.mid   *17*      Using where; Using temporary; Using filesort

The result set is 640 rows. Is there any real good way to optimize this for larger table? Since this table and especialy this query will grow more in the future.

Wi开发者_Go百科ll using procedure in mysql helps any for this query?

Thank you very much

Try this:

SELECT a.id, X.chapterno, X.mid, X.hitcount
FROM 
(select chapterno,  max(hitcount) as hitcount 
from chapter    
WHERE mid = 2524
group by chapterno)X
INNER JOIN chapter a ON (a.chapterno = X.chapterno AND a.mid=X.mid)
ORDER BY X.chapterno DESC

This query will benefit from index on(chapterno,hitcount). Also, from your data(many records with same value of MID) and EXPLAIN output, it seems that you don't need an index on mid (I believe mid_idx is an index on mid) because it's not selective enough...

CREATE INDEX on all three of those fields: t1.id, t1.hitcount, t1.chapterno

Try creating an index on Mid and ChapterNo. Possibly even on HitCount but keep in mind that indexes hurt performance on inserts/updates so don't go about creating indexes everywhere. I'd say, start with Mid, test, and then create index on Chapter.

ALTER TABLE `chapter` ADD INDEX `cindex_1` (`mid` ASC, `hitcount` ASC, `chapterno` DESC);
ALTER TABLE `chapter` ADD INDEX `cindex_2` (`mid` ASC, `chapterno` ASC, `hitcount` DESC);

The first index optimizes the main query, while the second optimizes the subquery.

Here's what I did:

SELECT c1.ID, c1.ChapterNo, c1.HitCount 
FROM chapter c1 JOIN (
SELECT ChapterNo, max(HitCount) max 
FROM chapter c2 
WHERE MID=2524 
GROUP BY ChapterNo) c3 
ON c1.ChapterNo=c3.ChapterNo AND c1.HitCount=c3.max;

With 2 indexes: One on ChapterNo, HitCount and one on MID

Returns instantly with 100k rows.