MySQL Bugs: #31373: Unusing index in select query

Bug #31373	Unusing index in select query
Submitted:	3 Oct 2007 14:42	Modified:	10 Nov 2007 23:51
Reporter:	Isataev Vladymir	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27-log	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE `support_tickets` (
  `TICKETID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
  `PARENTID` mediumint(8) unsigned zerofill NOT NULL default '00000000',
  `SUBJECT` varchar(250) NOT NULL default 'No Subject Entered',
  `DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `SENDER` varchar(100) NOT NULL default '',
  `RECEIVER` varchar(100) NOT NULL default '',
  `REALRECEIVER` varchar(100) NOT NULL default '',
  `CODING` varchar(25) NOT NULL default '',
  `content` enum('plain','html') default NULL,
  `TYPE` enum('Ticket','Staff Comment','Client Followup','Staff Reply') NOT NULL default 'Staff Comment',
  `antispam_verdict` char(1) default NULL,
  `antispam_score` float(7,6) unsigned default NULL,
  `antispam_user_mark` set('S','H') default NULL,
  PRIMARY KEY  (`TICKETID`),
  KEY `PARENTID` (`PARENTID`),
  KEY `DATE` (`DATE`),
  KEY `SENDER` (`SENDER`),
  KEY `RECEIVER` (`RECEIVER`),
  KEY `normal_subject` (`SUBJECT`),
  FULLTEXT KEY `SUBJECT` (`SUBJECT`)
) ENGINE=MyISAM AUTO_INCREMENT=254611 DEFAULT CHARSET=latin1 ; 

it was noticed that after upgrading from mysql 4, some queryes works slow and lockes other queryes.

mysql> explain select subject from support_tickets WHERE support_tickets.SUBJECT like '%werwe%' ;
+----+-------------+-----------------+-------+---------------+----------------+---------+------+--------+--------------------------+
| id | select_type | table           | type  | possible_keys | key            | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------------+-------+---------------+----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | support_tickets | index | NULL          | normal_subject | 252     | NULL | 249752 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+----------------+---------+------+--------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select ticketid from support_tickets WHERE support_tickets.SUBJECT like '%werwe%' ;
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | support_tickets | ALL  | NULL          | NULL | NULL    | NULL | 249752 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

But on mysql 4 key normal_subject is using independent on selected fields.

How to repeat:
look above

Suggested fix:
idk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45. In case of the same problem can you, please, try to prove that 5.0.x plan with ALL runs slower? Or send a dump that can be used to demonstrate that.

So, i try ed on 5.0.45-Debian_1ubuntu2-log and it is the same problem. Key "normal_subject" is used only when selecting field `subject` or count(*). 
I will attach dump of table, so you can try to repeat it. Tnx.

Thank you for the feedback.

Please indicate accurate version of MySQL 4 you are working with. Also please provide output of EXPLAIN issued with MySQL 4.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".