15 Ways to Optimize Your SQL Queries

Previous article was on 10 Ways To Destroy A SQL Database that sort of teaches you what mistakes many company might make on their database that will eventually lead to a database destroy. In this article,  you will get to know 15 ways to optimize your SQL queries. Many ways are common to optimize a query while others are less obvious.

Indexes

Index your column is a common way to optimize your search result. Nonetheless, one must fully understand how does indexing work in each database in order to fully utilize indexes. On the other hand, useless and simply indexing without understanding how it work might just do the opposite.

Symbol Operator

Symbol operator such as >,<,=,!=, etc. are very helpful in our query. We can optimize some of our query with symbol operator provided the column is indexed. For example,

9 thoughts on “15 Ways to Optimize Your SQL Queries

  1. You are wrong about the NOT opertor. If you think about it you will realize that you can determine if there are NO black marbles in a bowl just as fast as you can determine if there is at least one black marble. There is no need to examine every marble; you can stop as soon as I find one black marble.

    NOT EXISTS is exactly that: an EXISTS test that is logically negated. It's possible that a NOT EXISTS (or NOT LIKE or NOT IN) test will examine every row/character/list member if the searched item is not present, but that will happen for both EXISTS and NOT EXISTS.

  2. MAX and MIN do not "look for the maximum or minimum value in a column," and they aren't operators. The MIN and MAX functions are aggregate functions that operate on the selected rows (or groups of rows if GROUP BY is used). SELECT MAX(col) FROM table will find the maximum value of col, but the functions are more general than that. Indexes are expensive to maintain, and indexing columns just to speed up MIN and MAX is not great advice.

  3. @Greg : I agree with you that indexing columns just to speed up MIN and MAX is not a good advice. May be there is a misunderstanding on that point. I meant that MAX and MIN can be used on indexed column for better speed. Deliberating indexing a column because of a MIN or MAX is pure, NO NO. Thanks for the feedback 🙂

    Well, regarding the NOT operator, if there are any algorithm available in the world that work like a human. May be your theory might just hit the right spot.

  4. @Veera : To make thing simple. If there are 15 available in that column it will directly point towards 15 (if there are no exact value, it will just be similar as <16) instead of going through the whole row finding which is the highest value that is smaller than 16 (might be 15,14,13,12,11, etc. the DBMS do not know until it look through them). If there is a equal to symbol, it means that there is a probability that it will jump directly to 15 and return the result directly to you.

  5. nice post! i had no idea about #2 and #4...

    also, some discoveries i've made about queries:

    - UNION is slow, try to use stored procedures: i've not tested this one on MySQL, but in PostGreSQL, it is much faster to use a SP than an union. Example:
    SP:
    SELECT
    getValue(column1) AS name,
    Operation(column2) AS number_of_products

    is much much faster than:
    (SELECT
    name,
    0 AS number_of_products
    FROM customer
    WHERE a=b)
    UNION
    (SELECT
    0 AS name,
    number_of_products
    FROM products
    WHERE y=z)

    However, like i said, i've not tested this one on MySQL.

    - PHP and MySQL: ok, strictly this isn't a part of query optimization, but it is always better to just retrieve the rows we are interested in, instead of all rows. Why? Memory (PHP and MySQL) and bandwidth consumption.
    Example:
    best:
    SELECT id, name FROM customer
    instead of:
    SELECT * FROM customer
    (Assuming "customer" haves an id, name, last_name, description, login, password, e-mail, etc.)

    It also makes the code cleaner.

    INNER/LEFT/RIGHT JOIN: ok, this is an untested one. A while ago, i was asked to optimize a MySQL query. Originally, i used joins, but turned out it was much faster to use them in the WHERE part.
    Example:
    Slower:
    SELECT * FROM customer AS a LEFT JOIN products AS b ON a.id = b.id_customer
    Faster:
    SELECT * FROM customer AS a, products AS b
    WHERE a.id = b.id_customer

    I don't know how or why, but it turned out that first case was much slower (10 seconds) than the second case (3,4 seconds).
    It was however, a system already in production, so i hadn't the chance to play much with the query or with indexes 🙁

    The client however, was very happy with the results xD

    Greetings 😉

  6. However, if that particular column was never used for searching purposes, it gives no reason to index that particular column although it is given unique

    Um, I would say this is misleading too. What about unique columns that are frequently used in joins, but never searched?

  7. @JW : Yup, you don't need an index when no search is being done on a particular column. But if it is a unique and frequently use column, having an index will perform better.

    For join, it depends on what DBMS you use. In MySQL, indexes perform more efficient when your join have the same data type and size. Although you don't need its result, columns that frequently require in joins need DBMS to search for the matching partners. Hence, having indexes will be better in joins too but criteria to make it efficient depends on each individual implementation of DBMS.

  8. @unreal4u : Thanks for sharing 😀

    I'm not really familiar with stored procedure in MySQL at the moment since its quite new (MySQL v5.1 onwards). But in MySQL, stored procedure is used when a query is frequently used and have no data change. Hence, using a stored procedure will be more efficient. You can read Stored Procedure for more information. But it should work closely similar to PostGreSQL since the concept is the same.

    Ya, its not good to always use *. For security reason too other than optimization point of view. (i was writing an example so i was lazy, sorry about that)

    On the INNER/LEFT/RIGHT join, i also experience such situation with a few million of data table, when a join seems slower than a WHERE clauses. I read it somewhere before but i forgotten why is that so (should be at the documentation of MySQL).

    @Mehedi : Welcome 🙂

Comments are closed.