I've got two tables that are a 1 to 1 relation.
Table A has an
id column, and table B includes foreign key
A_id to table A, and a nullable
value column. I have a composite index on [value, A_id] on table B, and I'm running the following query:
SELECT B.A_id FROM B JOIN A ON B.A_id = A.id WHERE B.value IS NULL LIMIT 1000;
First of all, I understand the JOIN doesn't seem to serve a purpose here, I'm just illustrating the problem. In reality I need to select one other column from table A. If I get rid of the join, the query is instant.
There are 10 million rows in table B. This query takes more than 50 seconds to run. Yet the explain plan shows that table B is being accessed first, and is using the composite index. "Extra" shows "Using where; Using index". "Rows" shows almost 5 million rows.
What's going on here, and why doesn't the LIMIT cause this to run as nearly as quickly as without the join?