Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
454 views
in Technique[技术] by (71.8m points)

optimization - Why does MySQL not use an index for a greater than comparison?

I am trying to optimize a bigger query and ran into this wall when I realized this part of the query was doing a full table scan, which in my mind does not make sense considering the field in question is a primary key. I would assume that the MySQL Optimizer would use the index.

Here is the table:


CREATE TABLE userapplication (
  application_id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL default '0',
  accountid int(11) NOT NULL default '0',
  resume_id int(11) NOT NULL default '0',
  coverletter_id int(11) NOT NULL default '0',
  user_email varchar(100) NOT NULL default '',
  account_name varchar(200) NOT NULL default '',
  resume_name varchar(255) NOT NULL default '',
  resume_modified datetime NOT NULL default '0000-00-00 00:00:00',
  cover_name varchar(255) NOT NULL default '',
  cover_modified datetime NOT NULL default '0000-00-00 00:00:00',
  application_status tinyint(4) NOT NULL default '0',
  application_created datetime NOT NULL default '0000-00-00 00:00:00',
  application_modified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  publishid int(11) NOT NULL default '0',
  application_visible int(11) default '1',
  PRIMARY KEY  (application_id),
  KEY publishid (publishid),
  KEY application_status (application_status),
  KEY userid (userid),
  KEY accountid (accountid),
  KEY application_created (application_created),
  KEY resume_id (resume_id),
  KEY coverletter_id (coverletter_id),
 ) ENGINE=MyISAM ;

This simple query seems to do a full table scan:

SELECT * FROM userapplication WHERE application_id > 1025;

This is the output of the EXPLAIN:

+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | userapplication | ALL  | PRIMARY       | NULL | NULL    | NULL | 784422 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+`

Any ideas how to prevent this simple query from doing a full table scan? Or am I out of luck?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

MyISAM tables are not clustered, a PRIMARY KEY index is a secondary index and requires an additional table lookup to get the other values.

It is several times more expensive to traverse the index and do the lookups. If you condition is not very selective (yields a large share of total records), MySQL will consider table scan cheaper.

To prevent it from doing a table scan, you could add a hint:

SELECT  *
FROM    userapplication FORCE INDEX (PRIMARY)
WHERE   application_id > 1025

, though it would not necessarily be more efficient.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...