Bug #48886 MySQL doesn't seem to optimize "less than empty string"
Submitted: 19 Nov 2009 0:38 Modified: 19 Nov 2009 5:27
Reporter: Serge Igitov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 2009 0:38] Serge Igitov
Description:
Just a little suggestion for better optimization (if not implemented yet):

How to repeat:
CREATE TABLE mytable (title TINYTEXT NOT NULL)
INSERT INTO mytable VALUES (...), (...), ...

EXPLAIN SELECT * FROM mytable WHERE title<''

Shows in 4.1:

id  select_type  table   type  possible_keys  key  key_len  ref  rows  Extra  
1   SIMPLE       mytable ALL   NULL           NULL NULL     NULL 1000  Using where

Suggested fix:
I think it would be better with something like "Impossible WHERE" (col<'' always FALSE) :-)

Dagdamor
[19 Nov 2009 5:27] Valeriy Kravchuk
Thank you for the feature request. Even MySQL 5.1.42 still does not have this optimization.