query with exist take so long

Question

From my debian terminal I try to execute in mysql client a query like:

SELECT * 
FROM stop_times_lazio_set2_withtime2 AS B  
WHERE EXISTS 
(SELECT * 
FROM stop_times_lazio_set2_emptytime2 AS A 
WHERE B.trip_id=A.trip_id);

table A contains around 3 million records. table B is a sub set of A of around 400000 records.

I'd like to select every records of A thats have a row "parent" with the same id (yes its not an unique/primary id)

Now it takes more than hours...now I'm around 2h and i still seen just a blinking pointer... is it the query correct? Even I can't access to others mysql client like phpmyadmin. There is any way to speed up the process? There is a way to check how many records are processed at running times?

score 2 · Answer 1 · answered Apr 02 '16 at 12:38

2

I guess you have already indexed trip_id? There is another way writing the query, maybe it helps:

SELECT *
FROM stop_times_lazio_set2_withtime
WHERE trip_id IN (SELECT trip_id FROM stop_times_lazio_set2_emptytime2)

answered Apr 02 '16 at 12:38

maraca

8,468
3
23
45

This should be faster than all the versions with joins, except when the query gets optimized to the same execution plan (which I don't know), then they will be the same. – maraca Apr 02 '16 at 12:48
trip_id isnt unique... do I need to add a primary key like an autoincrement? the index helps a lot the query? – JahStation Apr 02 '16 at 13:03
@JahStation No, unique is fine http://stackoverflow.com/questions/9393234/mysql-unique-field-need-to-be-index – maraca Apr 02 '16 at 13:05
Barry and Seblor's answer are exactly the same, but Barry's way is the way it should be written. And left join is not what you want... trust me, I wouldn't say it if I wasn't certain: try this query or look further... @JahStation – maraca Apr 02 '16 at 13:20
excuse me, but I means that ive not a unique field...so i need to add an unique field to index over this table or i wrong? – JahStation Apr 05 '16 at 14:53
@JahStation yes if it is really unique, you can search stack-overflow on indexing tables or if you have a very specific problem you can ask a new question. – maraca Apr 06 '16 at 18:35

score 0 · Answer 2 · answered Apr 02 '16 at 12:38

0

I would expect a straight JOIN to be much much faster...

SELECT B.* 
FROM stop_times_lazio_set2_withtime2 AS B
JOIN stop_times_lazio_set2_emptytime2 AS A ON B.trip_id=A.trip_id

answered Apr 02 '16 at 12:38

Barry

3,303
7
23
42

this don't give me the same result as my query, lots of duplicate entry – JahStation Apr 05 '16 at 14:51

score 0 · Answer 3 · answered Apr 02 '16 at 12:38

0

Why not using a simpler request ?

SELECT A.* 
FROM stop_times_lazio_set2_emptytime2 AS A, stop_times_lazio_set_withtime2 AS B
WHERE B.trip_id=A.trip_id;

With that many records, it will obviously take time.

You can actually prevent it by processing only a few at a time by adding this at the end of the request

LIMIT <beginning>, <number of records>

answered Apr 02 '16 at 12:38

Seblor

6,947
1
25
46

Yes, but I mean, you can make a script that will process some at a time with multiple requests, not all at once. – Seblor Apr 02 '16 at 13:13

score 0 · Answer 4 · edited Apr 02 '16 at 13:00

0

Did you tried "left join"??

sample:

select columns from withtime 
  left join emptytime on withtime.tripid=emptytime.tripid;

edited Apr 02 '16 at 13:00

RiggsFolly

93,638
21
103
149

answered Apr 02 '16 at 12:45

Emrullah kuştaşı

1
2

query with exist take so long

4 Answers4