Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Review practical examples of ANALYZE FORMAT=JSON output. Learn to identify performance bottlenecks by comparing estimated costs against actual execution metrics.
Customers who have ordered more than 1M goods.
ANALYZE FORMAT=JSON
SELECT COUNT(*)
FROM customer
WHERE
(SELECT SUM(o_totalprice) FROM orders WHERE o_custkey=c_custkey) > 1000*1000;The query takes 40 seconds over cold cache.
ANALYZE shows that 39.208 seconds were spent in the subquery, which was executed 150K times (for every row of outer table).
This page is licensed: CC BY-SA / Gnu FDL
Gain deep insight into query execution with JSON-formatted analysis. This command combines optimizer estimates with actual runtime statistics for precise performance tuning.
EXPLAIN: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 39872,
"table": {
"table_name": "customer",
"access_type": "index",
"key": "i_c_nationkey",
"key_length": "5",
"used_key_parts": ["c_nationkey"],
"r_loops": 1,
"rows": 150303,
"r_rows": 150000,
"r_total_time_ms": 270.3,
"filtered": 100,
"r_filtered": 60.691,
"attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
"using_index": true
},
"subqueries": [
{
"query_block": {
"select_id": 2,
"r_loops": 150000,
"r_total_time_ms": 39531,
"table": {
"table_name": "orders",
"access_type": "ref",
"possible_keys": ["i_o_custkey"],
"key": "i_o_custkey",
"key_length": "5",
"used_key_parts": ["o_custkey"],
"ref": ["dbt3sf1.customer.c_custkey"],
"r_loops": 150000,
"rows": 7,
"r_rows": 10,
"r_total_time_ms": 39208,
"filtered": 100,
"r_filtered": 100
}
}
}
]
}
}ANALYZEr_rows is provided for any node that reads rows. It shows how many rows were read, on average.
r_filtered is provided whenever there is a condition that is checked. It shows the percentage of rows left after checking the condition.
The most important data not available in the regular tabular ANALYZE statement are:
r_loops field. This shows how many times the node was executed. Most query plan elements have this field.
r_total_time_ms field. It shows how much time in total, in milliseconds, was spent executing this node. If the node has subnodes, their execution time is included.
For UPDATE and DELETE statements, top-level query_block.r_total_time_ms does include the time to make row deletions/updates but does NOT include the time to commit the changes.
r_buffer_size field. Query plan nodes that make use of buffers report the size of buffer that was used.
Starting from MariaDB 10.6.15, , , , MariaDB 10.11.5, , and (MDEV-31577), the following statistics are reported for InnoDB tables:
Only non-zero members are printed.
pages_accessed is the total number of buffer pool pages accessed when reading this table.
pages_updated is the total number of buffer pool pages that were modified during the execution of the statement.
pages_read_count is the number of pages that InnoDB had to read from disk for this table. If the query touches "hot" data in the InnoDB buffer pool, this value will be 0 and not present.
pages_prefetch_read_count Number of pages for which read-ahead was initiated. Not all such pages will necessarily be accessed.
pages_read_time_ms is the total time spent reading the table.
old_rows_read is the number of old row versions that InnoDB had to read. Old row version is the version of the row that is not visible to this transaction.
MariaDB starting with
SHOW ANALYZE FORMAT=JSON for <connection_id>
extends ANALYZE [FORMAT=JSON] <select> to allow one to analyze a query currently running in another connection.
filesort node reports whether sorting was done with LIMIT n parameter, and how many rows were in the sort result.
block-nl-join node has r_loops field, which allows to tell whether Using join buffer was efficient.
range-checked-for-each-record reports counters that show the result of the check.
expression-cache is used for subqueries, and it reports how many times the cache was used, and what cache hit ratio was.
union_result node has r_rows so one can see how many rows were produced after UNION operation and so forth.
This page is licensed: CC BY-SA / Gnu FDL
"r_engine_stats": {
"pages_accessed": integer,
"pages_updated": integer,
"pages_read_count": integer,
"pages_prefetch_read_count": integer,
"pages_read_time_ms": double,
"old_rows_read": integer
}Learn commands for query analysis. This section covers ANALYZE TABLE and EXPLAIN, used to view execution plans and optimize query performance.
Understand the historical context of EXPLAIN ANALYZE in MariaDB. Learn how this syntax maps to the modern ANALYZE statement for profiling query execution.
Outdated syntax
The syntax for the EXPLAIN ANALYZE feature was changed to ANALYZE statement, available since . See ANALYZE statement.
This page is licensed: CC BY-SA / Gnu FDL
Understand the 'Using buffer' strategy for UPDATE operations. Learn how MariaDB prevents infinite update loops when modifying indexed columns during a range scan.
This article explains the UPDATE statement's Using Buffer algorithm.
Consider the following table and query:
Babatunde
1000
Jolana
1050
Pankaja
1300
UPDATE employees SET salary = salary+100 WHERE salary < 2000;Suppose the employees table has an index on the salary column, and the optimizer decides to use a range scan on that index.
The optimizer starts a range scan on the salary index. We find the first record Babatunde, 1000. If we do an on-the-fly update, we immediately instruct the storage engine to change this record to be Babatunde, 1000+100=1100.
Then we proceed to search for the next record, and find Jolana, 1050. We instruct the storage engine to update it to be Jolana, 1050+100=1150.
Then we proceed to search for the next record ... and what happens next depends on the storage engine. In some storage engines, data changes are visible immediately, so we will find the Babatunde, 1100 record that we wrote at the first step, modifying it again, giving Babatunde an undeserved raise. Then we will see Babatunde again and again, looping continually.
In order to prevent such situations, the optimizer checks whether the UPDATE statement is going to change key values for the keys it is using. In that case, it will use a different algorithm:
Scan everyone with "salary<2000", remembering the rowids of the rows in a buffer.
Read the buffer and apply the updates.
This way, each row will be updated only once.
The Using buffer output indicates that the buffer as described above will be used.
This page is licensed: CC BY-SA / Gnu FDL
Understand the r_rows and r_filtered fields in analysis output. Learn how these actual runtime counters compare to the optimizer's rows and filtered estimates.
attached_condition checking
Consider a table access which does all three:
The access is performed as follows:
In MariaDB versions before 11.5, the counters were counted as follows:
that is,
r_rows is counted after Index Condition Pushdown check and Rowid Filter check.
r_filtered only counts selectivity of the attached_condition.
selectivity of the Rowid Filter is in rowid_filter.r_selectivity_pct.
Starting from (MDEV-18478), the row counters are:
r_index_rows counts the number of enumerated index tuples, before any checks are made
r_rows is the same as before - number of rows after index checks.
The selectivity counters are:
r_icp_filtered is the percentage of records left after pushed index condition check.
rowid_filter.r_selectivity_pct shows selectivity of Rowid Filter, as before.
r_filtered is the selectivity of attached_condition check, as before.
r_total_filtered is the combined selectivity of all checks.
in ANALYZE FORMAT=JSON output these members are placed as follows:
Whenever applicable, r_index_rows is shown. It is comparable with rows - both are numbers of rows to enumerate before any filtering is done.
If r_index_rows is not shown, r_rows shows the number of records enumerated.
Then, filtering members:
filtered is comparable with r_total_filtered: both show total amount of filtering.
ICP and its observed filtering. The optimizer doesn't compute an estimate for this currently.
attached_condition and its observed filtering.
This page is licensed: CC BY-SA / Gnu FDL
Learn to use the ANALYZE statement to execute a query and produce a performance report. This command reveals how close the optimizer's plan was to the actual execution.
"table": {
"table_name": "t1",
"access_type": "range",
"possible_keys": ...,
"key": "INDEX1",
...
"rowid_filter": {
...
"r_selectivity_pct": n.nnn,
},
...
"rows": 123,
"r_rows": 125,
...
"filtered": 8.476269722,
"r_filtered": 100,
"index_condition": "cond1",
"attached_condition": "cond2"
}"table": {
"table_name": ...,
"rows": 426,
"r_index_rows": 349,
"r_rows": 34,...
"filtered": 8.476269722,
"r_total_filtered": 9.742120344,...
"index_condition": "lineitem.l_quantity > 47",
"r_icp_filtered": 100,...
"attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
"r_filtered": 100ANALYZE produces an overview, while the ANALYZE FORMAT=JSON command provides a more detailed view of the query plan and the query execution.The syntax is
where the statement is any statement for which one can run EXPLAIN.
Consider an example:
Compared to EXPLAIN, ANALYZE produces two extra columns:
r_rows is an observation-based counterpart of the rows column. It shows how many rows were actually read from the table.
r_filtered is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition.
Let's consider a more complicated example.
Here, one can see that
For table customer, customer.rows=149095, customer.r_rows=150000. The estimate for number of rows we will read was fairly precise
customer.filtered=18.08, customer.r_filtered=9.13. The optimizer somewhat overestimated the number of records that will match selectivity of condition attached to customer table (in general, when you have a full scan and r_filtered is less than 15%, it's time to consider adding an appropriate index).
For table orders, orders.rows=7, orders.r_rows=10. This means that on average, there are 7 orders for a given c_custkey, but in our case there were 10, which is close to the expectation (when this number is consistently far from the expectation, it may be time to run ANALYZE TABLE, or even edit the table statistics manually to get better query plans).
orders.filtered=100, orders.r_filtered=30.03. The optimizer didn't have any way to estimate which fraction of records will be left after it checks the condition that is attached to table orders (it's orders.o_totalprice > 200*1000). So, it used 100%. In reality, it is 30%. 30% is typically not selective enough to warrant adding new indexes. For joins with many tables, it might be worth to collect and use for columns in question, this may help the optimizer to pick a better query plan.
Let's modify the previous example slightly
The output of orders.r_rows=NULL and orders.r_filtered=NULL shows that the table orders was never scanned. Indeed, we can also see customer.r_filtered=0.00. This shows that a part of WHERE attached to table customer was never satisfied (or, satisfied in less than 0.01% of cases).
ANALYZE FORMAT=JSON produces JSON output. It produces much more information than tabular ANALYZE.
ANALYZE UPDATE or ANALYZE DELETE will actually make updates/deletes (ANALYZE SELECT will perform the select operation and then discard the resultset).
PostgreSQL has a similar command, EXPLAIN ANALYZE.
The EXPLAIN in the slow query log feature allows MariaDB to have ANALYZE output of slow queries printed into the (see ).
JIRA task for ANALYZE statement, MDEV-406
This page is licensed: CC BY-SA / Gnu FDL
ANALYZE explainable_statement;ANALYZE SELECT * FROM tbl1
WHERE key1
BETWEEN 10 AND 200 AND
col1 LIKE 'foo%'\G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl1
type: range
possible_keys: key1
key: key1
key_len: 5
ref: NULL
rows: 181
r_rows: 181
filtered: 100.00
r_filtered: 10.50
Extra: Using index condition; Using whereANALYZE SELECT *
FROM orders, customer
WHERE
customer.c_custkey=orders.o_custkey AND
customer.c_acctbal < 0 AND
orders.o_totalprice > 200*1000+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 9.13 | Using where |
| 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | 10 | 100.00 | 30.03 | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ANALYZE SELECT *
FROM orders, customer
WHERE
customer.c_custkey=orders.o_custkey AND
customer.c_acctbal < -0 AND
customer.c_comment LIKE '%foo%' AND
orders.o_totalprice > 200*1000;+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 0.00 | Using where |
| 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | NULL | 100.00 | NULL | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+Get comprehensive query plans in JSON format. This output provides detailed optimizer data, including costs and attached conditions, not found in the tabular view.
EXPLAIN FORMAT=JSON is a variant of EXPLAIN command that produces output in JSON form. The output always has one row which has only one column titled "JSON". The contents are a JSON representation of the query plan:
The output of MariaDB's EXPLAIN FORMAT=JSON is different from EXPLAIN FORMAT=JSON in MySQL. The reasons for that are:
MySQL's output has deficiencies.
The output of MySQL's EXPLAIN FORMAT=JSON is not defined. Even MySQL Workbench has trouble parsing it (see this ).
MariaDB has query optimizations that MySQL does not have. This means that MariaDB generates query plans that MySQL does not generate.
TODO: MariaDB's output format description.
produces output like EXPLAIN FORMAT=JSON, but amended with the data from query execution.
This page is licensed: CC BY-SA / Gnu FDL
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE col1=1\G*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 1000,
"filtered": 100,
"attached_condition": "(t1.col1 = 1)"
}
}
}or
or
The EXPLAIN statement can be used either as a synonym for or as a way to obtain information about how MariaDB executes a SELECT, UPDATE or DELETE statement:
'EXPLAIN tbl_name' is synonymous with'[DESCRIBE](../describe.md) tbl_name' or'[SHOW COLUMNS](../show/show-columns.md) FROM tbl_name'.
When you precede a SELECT, UPDATE or a DELETE statement with the keywordEXPLAIN, MariaDB displays information from the optimizer about the query execution plan. That is, MariaDB explains how it would process the SELECT
shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN.
The runs a statement and returns information about its execution plan. It also shows additional columns, to check how much the optimizer's estimation about filtering and found rows are close to reality.
There is an online that you can use to share EXPLAIN and EXPLAIN EXTENDED output with others.
EXPLAIN can acquire metadata locks in the same way that SELECT does, as it needs to know table metadata and, sometimes, data as well.
Here are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT:
The select_type column can have the following values:
This column contains information on how the table is accessed.
This column consists of one or more of the following values, separated by ';'
Note that some of these values are detected after the optimization phase.
The optimization phase can do the following changes to the WHERE clause:
Add the expressions from the ON and USING clauses to the WHERE clause.
Constant propagation: If there is column=constant, replace all column instances with this constant.
Replace all columns from 'const' tables with their values.
The EXTENDED keyword adds another column, filtered, to the output. This is a percentage estimate of the table rows that will be filtered by the condition.
An EXPLAIN EXTENDED will always throw a warning, as it adds extra Message information to a subsequent statement. This includes what the SELECT query would look like after optimizing and rewriting rules are applied and how the optimizer qualifies columns and tables.
As synonym for DESCRIBE or SHOW COLUMNS FROM:
A simple set of examples to see how EXPLAIN can identify poor index usage:
SELECT on a primary key:
The type is const, which means that only one possible result could be returned. Now, returning the same record but searching by their phone number:
Here, the type is All, which means no index could be used. Looking at the rows count, a full table scan (all six rows) had to be performed in order to retrieve the record. If it's a requirement to search by phone number, an index will have to be created.
example:
ref_or_null Optimizationref_or_null is something that often happens when you use subqueries with NOT IN as then one has to do an extra check for NULL values if the first value didn't have a matching row.
This page is licensed: GPLv2, originally from
EXPLAIN tbl_name [col_name | wild]EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON]
{SELECT select_options | UPDATE update_options | DELETE delete_options}EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>UPDATEDELETEEXPLAIN EXTENDEDEXPLAIN PARTITIONS is useful only when examining queries involving partitioned tables. For details, see Partition pruning and selection.
ANALYZE statement performs the query as well as producing EXPLAIN output, and provides actual as well as estimated statistics.
EXPLAIN output can be printed in the slow query log. See EXPLAIN in the Slow Query Log for details.
EXPLAIN FOR CONNECTION is an alias for SHOW EXPLAIN FOR.
key_len
How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref
The reference that is used as the key value.
rows
An estimate of how many rows we will find in the table for each key lookup.
Extra
Extra information about this join.
PRIMARY
The SELECT is in the outermost query, but there is also a SUBQUERY within it.
SIMPLE
It is a simple SELECT query without any SUBQUERY or UNION.
SUBQUERY
The SELECT is a SUBQUERY of the PRIMARY.
UNCACHEABLE SUBQUERY
The SUBQUERY is UNCACHEABLE.
UNCACHEABLE UNION
The UNION is UNCACHEABLE.
UNION
The SELECT is a UNION of the PRIMARY.
UNION RESULT
The result of the UNION.
LATERAL DERIVED
The SELECT uses a
index_subquery
This is similar as ref, but used for sub queries that are transformed to key lookups.
index
A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table.
range
The table will be accessed with a key over one or more value ranges.
ref_or_null
Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. This happens usually with sub queries.
ref
A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn't match many rows.
system
The table has 0 or 1 rows.
unique_subquery
This is similar as eq_ref, but used for sub queries that are transformed to key lookups
Remove the used key columns from the WHERE (as this will be tested as part of the key lookup).
Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'.
Replace columns with other columns that has identical values: Example: WHERE a=b and a=c may be treated as 'WHERE a=b and a=c and b=c'.
Add extra conditions to detect impossible row conditions earlier. This happens mainly with OUTER JOIN where we in some cases add detection of NULL values in the WHERE (Part of 'Not exists' optimization). This can cause an unexpected 'Using where' in the Extra column.
For each table level we remove expressions that have already been tested when we read the previous row. Example: When joining tables t1 with t2 using the following WHERE 't1.a=1 and t1.a=t2.b', we don't have to test 't1.a=1' when checking rows in t2 as we already know that this expression is true.
No matching min/max row
During early optimization of MIN()/MAX() values it was detected that no row could match the WHERE clause. The MIN()/MAX() function will return NULL.
no matching row in const table
The table was a const table (a table with only one possible matching row), but no row was found.
No tables used
The SELECT was a sub query that did not use any tables. For example a there was no FROM clause or a FROM DUAL clause.
Not exists
Stop searching after more row if we find one single matching row. This optimization is used with LEFT JOIN where one is explicitly searching for rows that doesn't exists in the LEFT JOIN TABLE. Example: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. As t2.not_null_column can only be NULL if there was no matching row for on condition, we can stop searching if we find a single matching row.
Open_frm_only
For information_schema tables. Only the frm (table definition file was opened) was opened for each matching row.
Open_full_table
For information_schema tables. A full table open for each matching row is done to retrieve the requested information. (Slow)
Open_trigger_only
For information_schema tables. Only the trigger file definition was opened for each matching row.
Range checked for each record (index map: ...)
This only happens when there was no good default index to use but there may some index that could be used when we can treat all columns from previous table as constants. For each row combination the optimizer will decide which index to use (if any) to fetch a row from this table. This is not fast, but faster than a full table scan that is the only other choice. The index map is a bitmask that shows which index are considered for each row condition.
Scanned 0/1/all databases
For information_schema tables. Shows how many times we had to do a directory scan.
Select tables optimized away
All tables in the join was optimized away. This happens when we are only using COUNT(*), MIN() and MAX() functions in the SELECT and we where able to replace all of these with constants.
Skip_open_table
For information_schema tables. The queried table didn't need to be opened.
unique row not found
The table was detected to be a const table (a table with only one possible matching row) during the early optimization phase, but no row was found.
Using filesort
Filesort is needed to resolve the query. This means an extra phase where we first collect all columns to sort, sort them with a disk based merge sort and then use the sorted set to retrieve the rows in sorted order. If the column set is small, we store all the columns in the sort file to not have to go to the database to retrieve them again.
Using index
Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.
Using index condition
Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the index level.
Using index condition(BKA)
Like 'Using index condition' but in addition we use batch key access to retrieve rows.
Using index for group-by
The index is being used to resolve a GROUP BY or DISTINCT query. The rows are not read. This is very efficient if the table has a lot of identical index entries as duplicates are quickly jumped over.
Using intersect(...)
For index_merge joins. Shows which index are part of the intersect.
Using join buffer
We store previous row combinations in a row buffer to be able to match each row against all of the rows combinations in the join buffer at one go.
Using sort_union(...)
For index_merge joins. Shows which index are part of the union.
Using temporary
A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.
Using where
A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong!
Using where with pushed condition
Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the row level.
Using buffer
The UPDATE statement will first buffer the rows, and then run the updates, rather than do updates on the fly. See for a detailed explanation.
id
Sequence number that shows in which order tables are joined.
select_type
What kind of SELECT the table comes from.
table
Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type
How rows are found from the table (join type).
possible_keys
keys in table that could be used to find rows in the table
key
The name of the key that is used to retrieve rows. NULL is no key was used.
DEPENDENT SUBQUERY
The SUBQUERY is DEPENDENT.
DEPENDENT UNION
The UNION is DEPENDENT.
DERIVED
The SELECT is DERIVED from the PRIMARY.
MATERIALIZED
The SUBQUERY is MATERIALIZED.
Materialized tables will be populated at first access and will be accessed by the primary key (= one key lookup). Number of rows in EXPLAIN shows the cost of populating the table
ALL
A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows.
const
There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants.
eq_ref
A unique index is used to find the rows. This is the best possible plan to find the row.
filter
A second index is being used with the Rowid Filtering Optimization.
fulltext
A fulltext index is used to access the rows.
index_merge
A 'range' access is done for several index and the found rows are merged. The key column shows which keys are used.
const row not found
The table was a system table (a table with should exactly one row), but no row was found.
Distinct
If distinct optimization (remove duplicates) was used. This is marked only for the last table in the SELECT.
Full scan on NULL key
The table is a part of the sub query and if the value that is used to match the sub query will be NULL, we will do a full table scan.
Impossible HAVING
The used HAVING clause is always false so the SELECT will return no rows.
Impossible WHERE noticed after reading const tables.
The used WHERE clause is always false so the SELECT will return no rows. This case was detected after we had read all 'const' tables and used the column values as constant in the WHERE clause. For example: WHERE const_column=5 and const_column had a value of 4.
Impossible WHERE
The used WHERE clause is always false so the SELECT will return no rows. For example: WHERE 1=2
DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | YES | | NULL | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+CREATE TABLE IF NOT EXISTS `employees_example` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(40) NOT NULL,
`position` varchar(25) NOT NULL,
`home_address` varchar(50) NOT NULL,
`home_phone` varchar(12) NOT NULL,
`employee_code` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `employee_code` (`employee_code`),
KEY `first_name` (`first_name`,`last_name`)
) ENGINE=Aria;
INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
VALUES
('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');
SHOW INDEXES FROM employees_example;
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees_example | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| employees_example | 0 | employee_code | 1 | employee_code | A | 7 | NULL | NULL | | BTREE | | |
| employees_example | 1 | first_name | 1 | first_name | A | NULL | NULL | NULL | | BTREE | | |
| employees_example | 1 | first_name | 2 | last_name | A | NULL | NULL | NULL | | BTREE | | |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees_example | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees_example | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | tbl | index | NULL | a | 5 | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)SELECT * FROM table_name
WHERE key_column=expr OR key_column IS NULL;