MYSQL how to speed up requests using EXISTS

Question

i have an SQL Requests:

SELECT DISTINCT id_tr 
FROM planning_requests a 
WHERE EXISTS(
        SELECT 1 FROM planning_requests b 
        WHERE a.id_tr = b.id_tr 
        AND trainer IS NOT NULL 
        AND trainer != 'FREE' 
      ) 
AND EXISTS(
        SELECT 1 FROM planning_requests c
        WHERE a.id_tr = c.id_tr 
        AND trainer IS NULL 
    )

but this requests take 168.9490 sec to execute for returning 23162 rows of 2545088 rows should i use LEFT JOIN or NOT IN ? and how can i rewrite it thx

Why don't you give it a try for yourself? You might actually learn something. — yunzen, Jan 26 '14 at 14:52
http://stackoverflow.com/questions/21363523/mysql-how-to-find-if-at-least-one-row-from-cross-reference-table-is-null-or-crit/21363586#21363586 — juhnz, Jan 26 '14 at 15:36

score 0 · Accepted Answer · answered Jan 26 '14 at 14:51

0

You can speed this up by adding indexes. I would suggest: planning_requests(id_tr, trainer).

You can do this as:

create index planning_requests_id_trainer on planning_requests(id_tr, trainer);

Also, I think you are missing an = in the first subquery.

EDIT:

If you have a lot of duplicate values of id_tr, then in addition to the above indexes, it might make sense to phrase the query as:

select id_tr
from (select distinct id_tr
      from planning_requests
     ) a
where . . .

The where conditions are being run on every row of the original table. The distinct is processed after the where.

answered Jan 26 '14 at 14:51

Gordon Linoff

1,242,037
58
646
786

good informations, i compare now with distinct then i will try to create an index. shoud i call planning_requests_id_trainer in request or its automatic ? – juhnz Jan 26 '14 at 15:12
without distinct on first id_tr , request is really too much long and froze mysql server xD – juhnz Jan 26 '14 at 15:19
@juhnz . . . It froze the SQL server even with the index in the answer? – Gordon Linoff Jan 26 '14 at 15:23
no i just try without index atm, i just have to create index ? or should i call index in request ? – juhnz Jan 26 '14 at 15:24
Just create the index. MySQL will know what to do with it. – Gordon Linoff Jan 26 '14 at 15:29
creating index through phpmyadmin is very slow , is better to use mysql.exe to do it ? – juhnz Jan 26 '14 at 15:44
I would do it through mysql.exe, but it will probably take a few minutes to create the index -- you have a lot of data. – Gordon Linoff Jan 26 '14 at 15:45
index improved seriously performance thx (>2Million records > 1hour ;p) – juhnz Jan 27 '14 at 08:59

score 0 · Answer 2 · answered Jan 26 '14 at 16:07

0

I think your query can be simplified to this:

SELECT DISTINCT a.id_tr 
FROM planning_requests a
JOIN planning_requests b
ON b.id_tr = a.id_tr
AND b.trainer IS NULL
WHERE a.trainer < 'FREE'

If you index planning_requests(trainer), then MySQL can utilize an index range to get all the rows that aren't FREE or NULL. All numeric strings will meet the < 'FREE' criteria, and it also won't return NULL values.

Then, use JOIN to make sure each record from that much smaller result set has a matching NULL record.

For the JOIN, index planning_requests(id_tr, trainer).

It might be simpler if you don't mix types in a column like FREE and 1.

answered Jan 26 '14 at 16:07

Marcus Adams

53,009
9
91
143

and if i trainer can be FREE and FREE2, SELECT DISTINCT a.id_tr FROM planning_requests a JOIN planning_requests b ON b.id_tr = a.id_tr AND b.trainer IS NULL WHERE a.trainer < 'FREE' OR a.trainer < 'FREE2' > does it work ? VALUE OR TRAINER EQUALS NULL OR VARCHAR (FREE,FREE2,1,2,3...) – juhnz Jan 26 '14 at 16:45
with exists requests : 23162 returns and with your requests 30907 returns – juhnz Jan 26 '14 at 16:59
What's `FREE2`? We only know what you tell us. Best way to ask how to optimize a query is to tell us what you're doing, not how you're doing it. This means, schema, sample data, and record counts. – Marcus Adams Jan 26 '14 at 19:20

MYSQL how to speed up requests using EXISTS

2 Answers2