Slow query

[Gerry Whitmarsh]

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