In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form:
SELECT table1.attrib1
FROM table2, table1
WHERE table2.status = 'Active'
AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )
The output of EXAMINE
tells me that the index on table2.table1
is used, but the index on table1.attrib1
isn't used to execute the query. Instead all existing rows of table1
are accessed (full table scan). The expected result contains only a few rows.
It's a commercial software, so I can't change the query. But I could add indexes or change database parameters.
Question: What can I do to speed up this query?
Best Answer
Since your situation is
Your best shot (really your only shot) would be
You may or may not see a change since the
is really a JOIN clause due to having the two tables on opposite sides of the
=
sign. The CONCAT on the right side would also trigger a full table scan. Adding the compound index onstatus
andtable1
seems to be the only option left.