user3470727 user3470727 - 1 month ago 15
PHP Question

SQL query take 60 seconds to complete

I have this query for a table with 14 thousands rows... took forever to complete the output. Here is the code:

$query="SELECT j6.Name, j.id,
SUM(j6.pts) + IFNULL(j.pts, 0) AS total
FROM joueurs2006 j6 LEFT OUTER JOIN joueurs j
ON (j.Name = j6.Name)
WHERE j6.pts != 0 GROUP BY j6.Name
ORDER BY total DESC
LIMIT 0,15";


What I want to do is compile the stats from past seasons (all stored in joueurs2006) and add stats from the current one (joueurs). It worked well for several years, but now with 14K of rows in the table, it takes forever to complete. Thanks for the tips!

Answer

This is your query:

SELECT j6.Name, j.id, SUM(j6.pts) + IFNULL(j.pts, 0) AS total
FROM joueurs2006 j6 LEFT OUTER JOIN
     joueurs j
     ON j.Name = j6.Name
WHERE j6.pts <> 0
GROUP BY j6.Name
ORDER BY total DESC
LIMIT 0, 15;

For this query, you want indexes on joueurs2006(name, pts) and joueurs(name, pts). That should work better.