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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.1+
• OS: Any
• 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.