How to speed up query containing concat() in WHERE clause? 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?
Since your situation is


product is commercial
you cannot change the query
you cannot change table layout


Your best shot (really your only shot) would be

ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1);


You may or may not see a change since the 

AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )


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 on status and table1 seems to be the only option left.