I have a query where one table has ~10 million rows and the other two are <20 in each table.
SELECT a.name, b.name, c.total
FROM smallTable1 a, smallTable2 b, largeTable c
WHERE c.id1 = a.id AND c.id2 = b.id;
largeTable
has columns (id, id1, id2, total)
and ~10 million rows
smallTable1
has columns (id, name)
smallTable2
has columns (id, name)
Right now it takes 5 seconds to run.
Is it possible to make it much faster?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `largeTable` ADD INDEX `largetable_idx_id1_id2` (`id1`,`id2`);
ALTER TABLE `smallTable1` ADD INDEX `smalltable1_idx_id` (`id`);
ALTER TABLE `smallTable2` ADD INDEX `smalltable2_idx_id` (`id`);
SELECT
a.name,
b.name,
c.total
FROM
smallTable1 a,
smallTable2 b,
largeTable c
WHERE
c.id1 = a.id
AND c.id2 = b.id