CODEWITHSUNDEEP
mysqloptimization
user1343099
user1343099

Reputation: 49

Improve Mysql query performance

I have this high scores table

CREATE TABLE IF NOT EXISTShighscores( lidint(11) NOT NULL, usernamevarchar(50) NOT NULL, useridint(6) NOT NULL, scoreint(16) NOT NULL, dateaddedtimestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (lid,username), KEYscore(lid,score) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and it has just over 2 million rows. When I run the following query it takes about 4.5 seconds. I'm hoping someone here has some advice on how I can improve the speed, by changing the query, adding/ modifying indexes, or maybe changing the storage engine?

SELECT h.userid,h.username AS user ,count(h.username) AS scorecount FROM highscores h INNER JOIN (select lid, min(score) AS maxscore FROM highscores group by lid) t on h.lid = t.lid and h.score = t.maxscore group by h.username ORDER BY scorecount DESC

as requested here is the EXPLAIN: EXPLAIN

Upvotes: 0

Views: 75

Answers (3)

user1343099
user1343099

Reputation: 49

What I Ended up doing was Caching the query, having it update every 10 min. There may be a better solution, but I could not get the query to be any faster.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is your query:

SELECT h.userid, h.username AS user, count(h.username) AS scorecount
FROM highscores h INNER JOIN
     (select lid, min(score) AS maxscore
      FROM highscores
      group by lid
     ) t
     on h.lid = t.lid and h.score = t.maxscore
group by h.username
ORDER BY scorecount DESC;

This seems to be answering the question: "How many times does a user have the maximum (or minimum) score in the table?". This suggests that you can rewrite the query as:

select hs.userid, hs.username, count(*) as scorecount
from highscores hs
where not exists (select 1 from highscores hs2 where hs2.lid = hs.lid and hs2.score > hs.score)
group by hs.userid, hs.username
order by scorecount desc;

Your existing indexes should work well on this. An index on userid, username would help some database engines, but I don't think MySQL would take advantage of it.

Upvotes: 0

Rheii
Rheii

Reputation: 54

sir, i dont think adjusting your query is your right solution here. you just said you had 2 million rows in that score table. can you imagine how long it would take for your queries to check cells/rows of each row whenever you execute them?

what you need is database normalization and proper way of designing your tables

http://en.wikipedia.org/wiki/Database_normalization

Upvotes: -1

Related Questions