Slow SQL query

[stubarny]

Hello,

 

I have the following code which is running very slowly (about 6 full seconds). Currently it is joining on millions of rows and then limiting to 100 rows at the end which seems a waste of resources?

 

Please could you show me how rewrite the query so that does it starts with the "ORDER BY" and "DESC LIMIT" operations and only then does the LEFT OUTER JOIN operation.

 

$query ="SELECT * FROM research_job_searches_results 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.research_job_search_keyphrase_1 = '$mysql_real_escape_string_q_job_category_with_spaces') ORDER BY research_job_searches_results.research_job_searches_results_index DESC LIMIT 0, $sql_number_of_records_limit";

 

Many thanks for your help,

 

Stu

[mikosiko]

Please could you show me how rewrite the query so that does it starts with the "ORDER BY" and "DESC LIMIT" operations and only then does the LEFT OUTER JOIN operation.

 

there is not such thing.

 

what the EXPLAIN of that sentence shows?

 

are there indexes defined for both tables?... which ones?

[stubarny]

there is not such thing.

 

Thank you, I will rework another solution.