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?
Best Answer
Even if both tables have the appropriate indexes on
A.id
andA_id
you still may need an index onB.value
to get decent performance. I've reformatted your query for readability.It appears that A_id is a foreign key for a many to one relationship from B to A. If that is the case we don't need to reference table
A
asA_id
will beNOT NULL
if the tables would join.If
B.value
is rarely NULL you may not 1000 records inB
that whereB.value
is NULL. You can count the records, but not get a distribution of the records with the query.An index on
B.value
would likely speed up the query. A limit of 1000 is significantly larger than I have used on a production system. Try the query with smaller limits like 1, 2, 5, 10, 20, 50 or 100.Problems with query performance are best investigated with an EXPLAIN PLAN of the query. This will tell you how the database is executing the query. I've seen the optimizer choose rather strange plans, usually providing better performance than the plan I expected. Rarely, the optimizer will choose a really bad plan. Without the right indexes and statistics, the optimizer may not be able to generate a plan the performs well.