MySQL Forums
Forum List  »  Performance

Slow query
Posted by: Gerry Whitmarsh
Date: December 11, 2015 01:00PM

Hi,

Can anyone please tell me what is happening here?

I have a table “clients” with a client_id and an archive table with buyer and seller fields – same data types as client_id.
If I run this query on the buyer the result is 4,500 rows in 0.5 sec. Fine
SELECT * FROM clients WHERE
( exists (select 1 FROM archive where (archive.buyer = clients.client_id)))

This query on the seller returns roughly the same number of rows, also in 0.5 sec. Also fine.
SELECT * FROM clients WHERE
( exists (select 1 FROM archive where (archive.seller = clients.client_id)))

But, if I try and combine the two queries it returns 7,800 rows which is ok, but it took 2.5 minutes.
SELECT * FROM clients WHERE
( exists (select 1 FROM archive where (archive.seller = clients.client_id OR archive.buyer = clients.client_id)))

This is a simplified query to which I am running, the real one takes ages.
Clients.client_id is a primary key and buyer and seller are also indexed. When querying just buyer or just seller MySQL uses the archive indexes so why is MySQL using full table scans on the archive table if querying both buyer and seller?
I have included the archive table in the primary query instead of a sub query (I had to use distinct though… ahhhh) and it flies but I really would like to know what is happening.

Thanks,
Gerry.
MySQL 5.6

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query
1595
December 11, 2015 01:00PM
757
December 11, 2015 05:00PM
743
December 11, 2015 06:56PM
652
December 12, 2015 07:34AM
709
December 14, 2015 02:34AM
614
December 15, 2015 08:48AM
681
December 15, 2015 10:57AM
767
December 16, 2015 02:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.