How to optimize a slow query? I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This means that we have an ever growing database. With regular intervals our customers do searches for different things, for example a certain material used in a car. These queries have started to take really long time to execute and we dont really know how to fix them. So my question is there anything we can do to optimize this query? I'll provide the EXPLAIN and then the query itself.
You have three potential areas you could address: LIKE operators, subqueries, and OR operators.

Regarding LIKE operators:

See https://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx


  
  If your filter criteria uses equals = and the field is indexed, then
  most likely it will use an INDEX/CLUSTERED INDEX SEEK
  If your filter criteria uses LIKE, with no wildcards (like if you had
  a parameter in a web report that COULD have a % but you instead use
  the full string), it is about as likely as #1 to use the index. The
  increased cost is almost nothing.
  If your filter criteria uses LIKE, but with a wildcard at the
  beginning (as in Name0 LIKE '%UTER') it's much less likely to use the
  index, but it still may at least perform an INDEX SCAN on a full or
  partial range of the index.
  HOWEVER, if your filter criteria uses LIKE, but starts with a STRING
  FIRST and has wildcards somewhere AFTER that (as in Name0 LIKE
  'COMP%ER'), then SQL may just use an INDEX SEEK to quickly find rows
  that have the same first starting characters, and then look through
  those rows for an exact match.
  


Regarding Subqueries

I would recommend inserting all of your subquery data into a temporary table first. Then, join the temporary table to your main select query.

See https://stackoverflow.com/questions/921931/why-is-inserting-into-and-joining-temp-tables-faster

Regarding OR operators:

Regarding performance, a question you should ask yourself is if you should use the OR operator or UNION operator as they both can have the same result.

See http://www.sql-server-performance.com/2011/union-or-sql-server-queries/


  
  Selecting all columns: OR is faster than UNION
  Non-Clustered and Clustered Index Columns only: no difference
  Selecting all columns for different fields: no difference
  Selecting Clustered index columns for different fields: UNION is faster than OR