Creating indexes for an sql query

[stubarny]

Hello,

 

Please could you tell me how to identify indexes that I need to setup when using JOINS as below? Do I just need to create an index for each column name mentioned below or should I be somehow joining some columns within the same index?

 

Many thanks,

 

Stu

 

$query ="SELECT * FROM research_job_searches_results 
LEFT OUTER JOIN 
recruitment_situations ON recruitment_situations.recruitment_situation_index = research_job_searches_results.research_job_searches_results_recruitment_situation_index 
LEFT OUTER JOIN 
company_website_addresses ON research_job_searches_results.research_job_searches_results_company_name = company_website_addresses.company_website_address_company_name 
LEFT OUTER JOIN 
research_job_searches ON research_job_searches.research_job_search_index = research_job_searches_results.research_job_searches_results_research_job_search_index 
WHERE 
(research_job_searches_results_index_of_original_result = 0 AND company_website_addresses.company_website_address_status = 'new') 
ORDER BY 
research_job_searches_results_index DESC LIMIT 100";

[fenway]

Those are some really long column names!

 

Why are you using LEFT JOIN?

 

In general, you need to index every column that will be used in a join -- which is typically only one of the two listed -- which ones depends on a great number of things.

[stubarny]

Thanks fenway - I've just created an individual index for every column mentioned and it's sped things up nicely!

 

Those are some really long column names!

 

LOL -  the column names combine the table name and the 'variable name' e.g. research_job_searches_results_company_name  is a column within table "research_job_searches_results" and the 'variable name' is "company_name" - it just makes it easier for me to know at a glance which table each variable orginates from. In hindsight maybe I should have used a seperating character e.g. "research_job_searches_results_-_company_name"

 

Why are you using LEFT JOIN?

 

I can't remember! - are you thinking I should be using INNER JOIN?

[fenway]

If you're not looking for non-matching rows, it will be much faster to user INNER JOINs.