current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
    
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. –  RolandoMySQLDBA May 26 at 18:48
    
key_buffer_size is 167772160 –  tangens May 26 at 20:45
    
server has 12 GB RAM –  tangens May 26 at 20:46
    
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 May 26 at 20:48
    
How much free RAM do you have ??? –  RolandoMySQLDBA May 26 at 20:48

1 Answer 1

up vote 0 down vote

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.

share|improve this answer
    
I tried the compound index, but the full table scan stays :-( Thank you for your answer. –  tangens May 26 at 18:19
    
@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. –  jkavalik May 26 at 19:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.