Extra in EXPLAIN printing - 'Impossible WHERE noticed after reading const tables'

mysql explain
sanchitkhanna26 picture sanchitkhanna26 · Feb 19, 2013 · Viewed 11.3k times · Source

I have a simple table called 'million_words'. It has one row with two columns -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY & word VARCHAR(50 NOT NULL.

I run this query -: EXPLAIN SELECT * FROM million_words WHERE word = '-anon'

The Extra column then prints : 'Impossible WHERE noticed after reading const tables, even though the row is clearly present in the table.

Whats wronf

Answer

Bhavik Shah picture Bhavik Shah · Feb 19, 2013

From MySQL documentation:

"Impossible WHERE noticed after reading const tables":
MySQL has read all const (and system) tables and notice that the WHERE clause is always false. Refer this

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. Refer this

Related questions

MySQL Explain: what's causing 'Using temporary; Using filesort'

I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly, I'm wondering why …

Read More
What does eq_ref and ref types mean in MySQL explain

When we prefix an SQL query with the keyword "explain" we get a table with some columns. Please tell me what is the "type" column. What does eq_ref and ref mean in that context.

Read More
MySQL EXPLAIN EXTENDED filtered column (obviously it's not a percentage)

I've been searching for this and they all state some sort of percentage, explain this: EXPLAIN EXTENDED SELECT * FROM PageAccess ORDER BY AccessId DESC LIMIT 20; SELECT COUNT(*) FROM PageAccess; Giving: id, select_type, table, type, possible_keys, key, key_len, …

Read More