CODEWITHSUNDEEP
mysqlperformance
user1372896
user1372896

Reputation: 534

SQL Query takes long when many rows

Using the query:

SELECT st.*
FROM surf_times st
WHERE st.Time = 
    (SELECT MIN(t.Time) 
    FROM surf_times t 
    WHERE t.SteamID = st.SteamID AND t.Map = st.Map) 
        AND st.Map = "surf_milkyway"
ORDER BY st.Time
LIMIT 15

Takes 0.15~ seconds with 7 results:

Showing rows 0 - 6 (7 total, Query took 0.1650 sec) [Time: 61.76 - 70.05]

When running this query on a table that will return 15 results, it suddenly takes 23 seconds 

Showing rows 0 - 14 (15 total, Query took 23.0710 sec) [Time: 47 - 52.13]

"surf_milkyway" has 17 total rows and takes 0.16~ seconds to execute.

"surf_beginner" has 2496 total rows and takes 23~ seconds to execute.

It takes 20+ seconds when the map searched for has many results in the table and takes long to compare and sort them. Is this something wrong with the query or the way the table is indexed etc?

Table is structured as:

SteamID - bigint(20)
Name - varchar(255)
Map - varchar(255)
Time - float
Date int(20)
id - int(11) - AUTO_INCREMENT, Primary

Upvotes: 0

Views: 53

Answers (2)

Rick James
Rick James

Reputation: 142298

Adding the following indexes will improve performance:

INDEX(Steamid, Map, Time),
INDEX(Map, Time)

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

An uncorellated query will always perform better than a corellated one, but if performance is still poor then we'd really need to see the table CREATE statements and the result of the EXPLAIN...

SELECT a.*
FROM surf_times a
Join
(SELECT steamid,map, MIN(Time) min_time
FROM surf_times 
Group by steamid,map) b
On b.steamid = a.steamid
And b.map = a.map
And b.min_time = a.time
    Where a.Map = "surf_milkyway"
ORDER BY a.Time
LIMIT 15

Upvotes: 1

Related Questions