Applicable to MySQL
You may have some queries taking too long to be executed. So, what steps can you take to get a better time execution?
Here are a few tips that can guide you.
Image by vectorjuice on Freepik
Let's remind the order of execution of a query:
1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT, OFFSET
- In general, check you are only taking the info you need, not more, meaning reduce your query to the fields you need, the tables needed etc.
- FROM and JOINS: What sorts of tables are you taking?
Size: do the tables have a large number of columns + lots of rows?
Indexes: Do we have necessary indexes on the used tables? Are the fields used in the JOINs having an index?
Check the order of the tables called in the FROM-JOIN: Are you calling the tables in a optimized way? This can be checked comparing results with EXPLAIN (how MySQL wants to execute the query) and EXPLAIN SELECT STRAIGHT_JOIN (your initial proposal)
Check that in the JOIN, you put first the smaller table in the ON comparison
For example, suppose you have a 'Client' table with 10000 records and another table 'Orders' with 200000 records, both having in common the clientId field.
If you make a JOIN with this clientId field, put first the clientId from 'Client', like like this:
SELECT c.clientId, c.firstname, c.lastname, o.orderId, ...
FROM Client c
JOIN Orders o ON c.clientId = o.clientId
-- > see that Client table (alias c) comes first in the ON condition
...;- Check the level of complexity of the WHERE conditions and report the WHERE conditions in the JOIN when possible
If there are different conditions, retrieve them 1 by 1 to see if one of them is specially long to execute and the cause of the slowness.
- Check the level of complexity of the SELECTs
Same: try to retrieve them to see if one of them is slowing down the exec.
Some functions used in the SELECT for transform purpose may have an impact, for example COUNT().
- Nothing is working? Try using FORCE INDEX and check speed. Syntax below to place it correctly:
SELECT field1, fields2...
FROM table_name1 FORCE INDEX (index_name1)
INNER JOIN table2 FORCE INDEX (index_name2)
ON table1.col_name=table2.col_name
WHERE ...;---------------------------------------------------------------------
Index - MySQL syntax:
1. Create an index on 1 field of a table.
CREATE INDEX index_name ON table_name (fieldname1);
-- With length to save space on the field:
CREATE INDEX index_name ON table_name (fieldname1(20));2. Create an index on 2 fields of a table:
CREATE INDEX index_name ON table_name (fieldname1,fieldname2);
-- With length to save space on the fields:
CREATE INDEX index_name ON table_name (fieldname1(20),fieldname2(20));Drop an index:
DROP index_name ON table_name;---------------------------------------------------------------------
last_query_cost tool: With this sentence you can quickly check the cost of your query and compare it to other options you may consider:
SHOW STATUS LIKE 'last_query_cost';
-------------------------------
Variable_name |Value
-------------------------------
Last_query_cost |617.617568---------------------------------------------------------------------
EXPLAIN: Use EXPLAIN, EXPLAIN ANALYZE, EXPLAIN SELECT STRAIGHT_JOIN to get more information on your query.
Syntax:
EXPLAIN
SELECT ... FROM ... WHERE;
EXPLAIN ANALYZE
SELECT ... FROM ... WHERE;
EXPLAIN
SELECT STRAIGHT_JOIN ... FROM ... WHERE;Useful links:
Comments