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
1384
December 11, 2015 01:00PM
683
December 11, 2015 05:00PM
664
December 11, 2015 06:56PM
556
December 12, 2015 07:34AM
632
December 14, 2015 02:34AM
520
December 15, 2015 08:48AM
597
December 15, 2015 10:57AM
662
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.