SQL query take 60 seconds to complete

Question

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!

I'd suggest reading a bit about [INDEXES](http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql) in case you haven't already. They're the quickest and easiest "speed up my query" solution. — Tyler Roper, Oct 14 '16 at 21:02
What kind of database (SQL Server, Access, MySQL, Oracle...) — Bob Kaufman, Oct 14 '16 at 21:03

score 0 · Accepted Answer · answered Oct 14 '16 at 21:03

0

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.

answered Oct 14 '16 at 21:03

Gordon Linoff

1,242,037
58
646
786

@user3470727 . . . How fast is it? – Gordon Linoff Oct 15 '16 at 02:24

SQL query take 60 seconds to complete

1 Answers1