Mysql query runs longer than long_query_time variable
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.