Imagine I have this SQL query and the table2 is HUGE.
select product_id, count(product_id)
from table1
where table2_ptr_id in (select id
from table2
where author is not null)
Will SQL first execute the subquery and load all the table2 into memory? like if table1 has 10 rows and table2 has 10 million rows will it be better to join first and then filter? Or DB is smart enough to optimize this query as it is written.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `table2` ADD INDEX `table2_idx_id_author` (`id`,`author`);
SELECT
table1.product_id,
count(table1.product_id)
FROM
table1
WHERE
EXISTS (
SELECT
1
FROM
table2
WHERE
(
table2.author IS NOT NULL
)
AND (
table1.table2_ptr_id = table2.id
)
)