Mysql query runs longer than long_query_time variable


I have this query in picture attached that runs longer than my long_query_time. Please can anyone recommend what index or indexes to create to speed it up?

Best Answer

Just looking at the query you have you are missing indexes for:

  • is_template
  • archived
  • market_segment
  • template_type

I am assuming the indexes you currently have are used for other queries correct?

I am thinking you probably need an index on the other tables name column.

ALTER TABLE `projects` ADD INDEX project_ix1( is_template , archived, market_segment, template_type );
ALTER TABLE `template_types` ADD INDEX templatetypes_ix1( alias );
ALTER TABLE `market_segments` ADD INDEX marketsegments_ix1( name );

You might want to break up the multi-column index, since this is only for the query you asked about, and I don't know what other queries you might have in the pipe. Let me know the results.