Advanced Database Query Optimization Quick Tips

Author: | Posted in Database, Quick Tips No comments

Databases tend to grow over time as they store more and more information. To ensure your database information can be accessed in the shortest time possible, you need to learn a few tricks to speed up data retrieval as per below:

  1. Index: Build your indexes properly to match the queries you are running. Run EXPLAIN to see the types of queries that are run and make sure that they all use an index where possible.
  • In Mysql , There are several type of indexes
  • Tree Indexes:: B-Tree, B+Tree, T-Tree, RB Binary Tree, R Tree
  • Hash Indexes:: (Memory)
  1. Partition: Partition your table. Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key. MySQL supports this internally from ver. 5.1.
  2. Slow Query Log:
    • Logs all queries that take more than long_query_time
    •  Can all those queries that doesn’t  use indexes with –long-queries-not-using-indexes
    • Can log slow admin commands
  3.  Intermediate Table: If necessary, build summary/intermediate tables that cache the costlier parts of your queries. Then run your queries against the summary/intermediate tables. Similarly, temporary in-memory tables can be used to store a simplified view of your table as a pre-processing stage.
  1. Normalization: It is key concept to optimize your database with some standards.
    • Good normalization prevent redundant data
    • Transaction database should be in 3 rd normal form.
  1. Table Optimization:
    • Use columns that are short as possible i.e. Use Int instead of BigInt
    • Define proper length of data type.
    • Define columns as not Null if possible.
  1. Remove Unused Memory:
    • Use Truncate command for empty the table content.
    • A null ALTER table to free up deleted rows.
  2. Query Optimizations: 
    • Use the slow query log to find slow queries.
    • Use EXPLAIN to determine queries are functioning appropriately.
    • Test your queries often to see if they are performing optimally – performance will change over time.
    •  Avoid count(*) on entire tables, it can lock the entire table.
    •  Make queries uniform so subsequent similar queries will use query cache.
    •  Use GROUP BY instead of DISTINCT when appropriate.
    • Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.
    • Keep indexes simple, do not reuse a column in multiple indexes.
    • Sometimes MySQL chooses the wrong index, use USE INDEX for this case
    • Check for issues using SQL_MODE=STRICT.
    • Use a LIMIT on UNION instead of OR for less than 5 indexed fields.
    • Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid the SELECT prior to update.
    • Use a indexed field and ORDER BY instead of MAX.
    • Avoid using ORDER BY RAND().
    •  LIMIT M,N can actually slow down queries in certain circumstances, use sparingly.
    • Use UNION instead of sub-queries in WHERE clauses.
    •  For UPDATES, use SHARE MODE to prevent exclusive locks.
    • On restarts of MySQL, remember to warm your database, to ensure that your data is in memory and queries are fast.
    •  Use DROP TABLE then CREATE TABLE instead of DELETE FROM to remove all data from a table.
    • Minimize the data in your query to only the data you need, using * is overkill most of the time.
    •  Consider persistent connections instead of multiple connections to reduce overhead.
    • Benchmark queries, including using load on the server, sometimes a simple query can have affects on other queries.
    • When load increases on your server, use SHOW PROCESSLIST to view slow/problematic queries.
    • Test all suspect queries in a development environment where you have mirrored production data.