MySQL Forums
Forum List  »  General

index usage dependent on in(group) content
Posted by: Allan Kelly
Date: March 22, 2009 02:28PM

Hi, I don't understand this behaviour. I'd like to ensure index usage (obviously!).

This simple DB has a main 'entries' table with just under 150,000 rows. Each row has a category column, which is type text. This could be optimised to type varchar(15) but I haven't done that as yet.

The queries below show index usage for 'select ... where category in( ... )' being dependent upon the in(group) content. Can you explain it please?! Thanks in advance, al.

> explain select title from entries where category IN("BUSINESS","SCOTLAND","WALES","FILMS")\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: entries
         type: range
possible_keys: i_cat_link
          key: i_cat_link
      key_len: 23
          ref: NULL
         rows: 24256
        Extra: Using where
1 row in set (0.02 sec)

> explain select title from entries where category IN("BUSINESS","SCOTLAND","WALES","FILMS","CANADA")\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: entries
         type: ALL
possible_keys: i_cat_link
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 138040
        Extra: Using where
1 row in set (0.00 sec)

> show create table entries\G
*************************** 1. row ***************************
       Table: entries
Create Table: CREATE TABLE `entries` (
  `category` text,
  `date_retrieved` text,
  `time_retrieved` text,
  `datetimestamp` int(11) default NULL,
  `site_url` text,
  `site_name` text,
  `title` text,
  `content` text,
  `entry_id` int(11) NOT NULL auto_increment,
  `pubDate` text,
  `link` text,
  `image` text,
  `category_title` varchar(20) default NULL,
  `tlw` text,
  `front_page` varchar(8) default NULL,
  PRIMARY KEY  (`entry_id`),
  KEY `i_site_url` (`site_url`(100)),
  KEY `i_cat_link` (`category`(20),`link`(100)),
  KEY `i_con_name` (`content`(100),`site_name`(50)),
  KEY `i_dts` (`datetimestamp`),
  KEY `i_url_dts` (`site_url`(100),`datetimestamp`),
  FULLTEXT KEY `ft_title_cont_tlw` (`title`,`content`,`tlw`)
) ENGINE=MyISAM AUTO_INCREMENT=140255 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Written By
Posted
index usage dependent on in(group) content
March 22, 2009 02:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.