[SOLVED] MySQL Query Efficiency

[bendurber]

Hi,

 

I need to optimise a query due to the size of the tables. 

 

To set the background I have three tables tbl_company(30k records), tbl_contact(50k records) and tbl_search_results(1M+ records).

A company may have many contacts and the table is joined by the field comp_id.

I have a list of companies in the search table and want to get all of the contacts associated to those companies.

 

The query below works fine for when the number of records in the search is small but it takes a lot longer when we increase it to 1000, 10000 and all of the companies (30000).

 

SELECT * FROM tbl_search_results r
INNER JOIN tbl_contact c ON r.result_link = c.comp_id /*(note, the company id is not the primary key)*/
WHERE search_id='590';

 

Any ideas as to how I can improve the efficiency would be most appreciated.

 

Thanks

 

Ben

[arianhojat]

Can you set the company id in the tbl_contact table to be Index key (not Primary, just set to Index)?

I assume result_link is Primary or Index-ed field'

 

are these MyISAM databases or InnoDB? maybe set to MyISAM too if dont need foreign keys, etc.

[bendurber]

I can and will change the company id field to an Indexed field.  The database is already set to MyISAM.

 

Is the query that I am using the best way or is there a more efficient way?

 

Thanks

[fenway]

Post the EXPLAIN output to make certain that you've done that correctly.

[bendurber]

id select_type table type possible_keys             key       key_len ref                     rows Extra

1 SIMPLE         r   ref   search_id,result_link        search_id      5   const             42672 Using where

1 SIMPLE         c ref   comp_id                   comp_id 5     db_hr.r.result_link     2           Using where

 

Once I ran this query it took around 2.5 seconds and that is to align all of the companies with contacts.  Given the previous results its excellent and I suspect I cant really do much more to improve it?

 

Thanks for you help

[fenway]

That's about as good as you can get... unless you've picked the wrong sized column types to index.