How to speed up this SQL Query?

Question

I am trying to fetch data from a SQL DB via PHPmyAdmin but it takes ages to excute my query. Do you have any idea how to speed up my query ?

My DB has the following structure

Table joueurs

| id_joueur | mail |

Table paires

| paires.annee | idj1 | idj2 |

Thanks alot

/* mail from all players being first memeber of a pair (idj1) 
in 2015, 2016 and 2017 and removing doubles*/

SELECT 
    mail
from 
    joueurs 
where 
    (joueurs.id_joueur in
        (SELECT idj1
        FROM paires
        WHERE  (paires.annee = 2015 OR paires.annee = 2016 OR paires.annee = 2017)
        )
    )
GROUP BY mail
HAVING COUNT(*) > 0

score 1 · Answer 1 · answered May 01 '18 at 09:04

1

I would remove the subquery and just join the two tables. And also where clause might be better with a condition like paires.annee > 2014 and paires.annee < 2018. But the only way to improve it is to add indices to the tables.

SELECT 
    j.mail
FROM
    joueurs j 
JOIN paires p on p.idj1 = j.id_joueur
WHERE p.annee > 2014 and p.annee < 2018  
GROUP BY j.mail
HAVING COUNT(*) > 0

answered May 01 '18 at 09:04

CynicalSection

694
4
8

Don't need the `having` clause, as with the [inner] join there will always be at least one. in fact, making it `SELECT DISTINCT` and dropping the group by would do the same thing. If this does not make things faster (which is likely), agree that indexes need to be reviewed. – Philip Kelley May 04 '18 at 14:19

Rajeev Ranjan · Answer 2 · 2018-05-01T09:02:15.900

0

You can use Exists on place of in, something like this

SELECT 
    j.mail
from 
    joueurs j
where 
   Exists
        (SELECT idj1
        FROM paires
        WHERE  idj1 =j.id_joueur and (paires.annee = 2015 OR paires.annee = 2016 OR paires.annee = 2017  )
        )    
GROUP BY j.mail
HAVING COUNT(*) > 0

EDIT: SQL Server IN vs. EXISTS Performance here you can read about Exists and IN

edited May 01 '18 at 09:02

answered May 01 '18 at 08:57

Rajeev Ranjan

497
4
16

Thank you Rajeev, but this does not seem to improve performance – Vic202 May 01 '18 at 09:14

score 0 · Answer 3 · answered May 01 '18 at 10:04

My initial recommendation is:

select distinct j.mail
from joueurs j
where exists (select 1
              from paires p
              where p.idj1 = j.id_joueur and p.annee in (2015, 2016, 2017)
             );

The having clause does nothing. For performance, you want an index on paires(idj1, annee).

If this still takes a long time, what happens if you remove the distinct? Are the results still acceptable?

How to speed up this SQL Query?

3 Answers3