0

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.

1
  • 2
    When trying to end on a date, instead of < "2015-02-19 23:59:59" you could more accurately use < "2015-02-20" and this will include that last second of the day.
    – RLF
    Feb 19, 2015 at 16:46

1 Answer 1

1

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
3
  • You realize al your links are about SQL-Server while the question is tagged with MySQL. Many of the recommendations are valid for MySQL as well (but just saying). Feb 19, 2015 at 17:20
  • About the likes, this query is used when searching for something in a table of materials. Our customers can search for anything and everything. Unless we can do the like in another way I cant see that part changing. Is there maybe a better way to do it? I've never done subqueries as temporary tables, are there any drawbacks to that approach? Aren't temporary tables written to disk, which is a lot slower than RAM?
    – user59890
    Feb 20, 2015 at 12:23
  • My point about the temporary table is that you have 10+ subqueries from the same two tables. If you could grab all that data in one pass, manipulate the data, and then throw it in your SELECT statement, it would be more efficient because your DB is currently forced to go back and forth to the same two tables over 10 times. You could also join one subquery and use that data in your SELECT statement if you wish, but regardless, attempting to cut back on the subqueries should be a performance objective for you.
    – Zach Allen
    Feb 20, 2015 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.