Index scans (“type: index”) are better than table scans, especially when “Extra: Using index” appears in the EXPLAIN plan which means a covering index is being used. Ultimately, Select_scan is a performance limiter which should decrease to allow increased overall QPS (queries per second). However, in some cases the value of Select_scan can increase after optimizing queries because MySQL is able to do more. From a performance perspective it’s safe to say that you always want to decrease Select_scan-zero is best. A SELECT results in a table scan when no index can be used to for the WHERE conditions. It’s not uncommon to see a server where 50% of all SELECT queries are Select_scan. Table scans (“type: ALL”) are usually (but not always) terrible for performance because reading an entire table is slow. For such tables, EXPLAIN lists “ALL” or “index” in the “type” column. It means MySQL did a table or index scan. Select_scan refers to the first table in a join, or the only table if there is only one table. Mysql> SHOW SESSION STATUS LIKE 'Select_%' Īs expected, Select_scan = 1 due to the table scan on t2. Mysql> SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2 ORDER BY t1.c2 Let’s clear the session metrics, execute the query, and check the select metrics: mysql> FLUSH STATUS The second table, t1, is neither a table scan nor a range scan, so no metric will be incremented because there are no select metrics for other join types. The first table, t2, is a table scan (“type: ALL”), so Select_scan will be incremented. See How MySQL executes ORDER BY by Sergei Petrunia for a beautifully illustrated explanation of MySQL ORDER BY. Step 3 refers to “Using temporary” in the EXPLAIN plan, and step 4 refers to “Using filesort”. It shows the query execution plan from the bottom up, per-node. MySQL is accessing table t2 first because it produces a better execution plan which you can see in detail by tracing the optimizer.īottom output is an “EXPLAIN tree” which is new as of MySQL 8.0. Note that the tables are listed differently in the FROM clause. Top output is a standard EXPLAIN plan with the first table on top, so t2 is the first table and t1 is the second table. > Nested loop inner join (cost=11.95 rows=26) Mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2 ORDER BY t1.c2 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 26 | 100.00 | Using temporary Using filesort | | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2 ORDER BY t1.c2 MySQL table join order is determined by the query planner (to create the best execution plan) not the JOIN clause clause (unless STRAIGHT_JOIN is used). Select_full_join, Select_full_range_join, and Select_range_check apply to the second and subsequent tables. Select_scan and Select_range apply to the first (or only) table. Using MySQL 8.0.22 Community, the test tables are: CREATE TABLE `t1` (Įach table has about 30 rows. The MySQL Select_% and Sort_% status variables (metrics) are: Let’s examine them in greater detail and with better examples.Ĭheck out my book to learn a lot more about MySQL performance: This page is a reboot of the original written 15 years ago in 2005.īack then, I must have been using MySQL 4.1 or 5.0.Ī lot changed in MySQL from 4.1 to 8.0, but it still has the same Select_% and Sort_% status variables which are equally important today in 2021.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |