Tips to speed up mysql queries
January 24, 2011 – 12:09 pm1. Instead of VARCHAR, BLOB or TEXT, Use CHAR type when possible. if values of a column have constant length: MD5-hash (32 symbols), ICAO or IATA airport code (4 and 3 symbols), BIC bank code (3 symbols), etc. Data in CHAR type columns can be found faster rather than in variable length data types columns.
2. Just because of you have too many columns, don’t split a table . In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row.
3. A column should be declared as “NOT NULL” only if it really needs it — thus you speed up table traversing a bit.
4. Instead of using PHP loop to fetch rows from database one by one only because of you can, use IN , e.g.
SELECT *
FROM `table`
WHERE `id` IN (1,6,18);
5. Use column default value, and insert only those values that differs from the default one. This reduces the query parsing time.
6. Think of storing users sessions data (or any other non-critical data) in MEMORY table — it’s very fast.
7. Images and other binary assets, for your web application should normally be stored as files. Store only a reference to the file rather than the file itself in the database.
8. If you need to calculate COUNT or SUM based on information from a lot of rows (articles rating, poll votes, user registrations count, etc.) frequently, then it would be great to create a separate table and update the counter in real time, which is much faster. If you need to collect statistics from huge log tables, take advantage of using a summary table instead of scanning the entire log table every time.
9. Don’t use REPLACE query (which is DELETE+INSERT and wastes ids): use INSERT … ON DUPLICATE KEY UPDATE instead (i.e. it’s INSERT + UPDATE if conflict takes place). The same technique can be used when you need first make a SELECT to find out if data is already in database, and then run either INSERT or UPDATE. Why to choose yourself — rely on database side.
10. Always divide complex queries into several simpler ones — they have more chances to be cached, so will be quicker.
11. Group several similar INSERTs in a long INSERT with multiple VALUES lists to insert several rows at a time: this way query will be quicker due to fact that connection + sending + parsing a query takes 5-7 times of actual data insertion (depending on row size).
But if that is not possible, use START TRANSACTION and COMMIT, if your database is InnoDB, otherwise you should use LOCK TABLES which will benefits the performance because the index buffer is flushed to disk only once, after all INSERT statements have completed; in this case unlock your tables each 1000 rows or so to allow other threads access to the table.
12. When loading a table from a text file, use LOAD DATA INFILE as it’s 20-100 times faster.
13. Avoid using ORDER BY RAND() to fetch several random rows. Fetch 10-20 entries (last by time added or ID) and make array_random() on PHP side.
14. Avoid using HAVING clause — it’s rather slow. In most of the cases, a DISTINCT clause can be considered as a special case of GROUP BY; so the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Also, if you use DISTINCT, try to use LIMIT (MySQL stops as soon as it finds row_count unique rows) and avoid ORDER BY (it requires a temporary table in many cases).