How to speed up query containing concat() in WHERE clause?

Question

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?

• RolandoMySQLDBA: I have two questions : 1) How much RAM does the DB Server have ? 2) What is your key_buffer_size ? Please run SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'; to find out.
• tangens: key_buffer_size is 167772160
• tangens: server has 12 GB RAM
• RolandoMySQLDBA: Run SELECT data_length,index_length FROM information_schema.tables WHERE table_schema = 'database_where_table2_resides' AND table_name = 'table2';. What is the output ?
• RolandoMySQLDBA: How much free RAM do you have ???

Answer 1

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.

• tangens: I tried the compound index, but the full table scan stays :-( Thank you for your answer.
• jkavalik: @tangens is it full table scan or full index scan? that should be a big difference unless table1 has only a couple of collumns. What you need is covering index on table1 - from query you posted index on (attrib1) should be enough, but if your real query uses more columns from table1, then those need to be added to the index to really utilize it.