Slow query
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
Subject
Views
Written By
Posted
Slow query
1881
December 11, 2015 01:00PM
879
December 11, 2015 05:00PM
844
December 11, 2015 06:56PM
789
December 12, 2015 07:34AM
886
December 14, 2015 02:34AM
716
December 15, 2015 08:48AM
778
December 15, 2015 10:57AM
899
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.