We have a table of this structure:
CREATE TABLE `data` (
`id` bigint(cool: NOT NULL auto_increment,
`brand_id` bigint(cool: NOT NULL default '0',
`pub_date` date NOT NULL default '0000-00-00',
`title` text NOT NULL,
PRIMARY KEY (`id`),
KEY `brand_id` (`brand_id`),
KEY `pub_date_2` (`pub_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
we do the following query
SELECT title
FROM data
WHERE brand_id =5 and pub_date >="2008-11-01" and pub_date < "2008-11-31"
When we do a query and do EXPLAIN we can see it correctly limits the results to scan only those with a brand id of 5. However it scans every row with a brand_id of 5 no matter what the pub_date is. With a few million rows in this table this gets very slow. Is there anyway to stop it scanning every row for each date and only scan the ones in the date range specified in the query? As thats why there is a key on the pub_date field I thought, to prevent row scans?
Thanks very much in advance
CREATE TABLE `data` (
`id` bigint(cool: NOT NULL auto_increment,
`brand_id` bigint(cool: NOT NULL default '0',
`pub_date` date NOT NULL default '0000-00-00',
`title` text NOT NULL,
PRIMARY KEY (`id`),
KEY `brand_id` (`brand_id`),
KEY `pub_date_2` (`pub_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
we do the following query
SELECT title
FROM data
WHERE brand_id =5 and pub_date >="2008-11-01" and pub_date < "2008-11-31"
When we do a query and do EXPLAIN we can see it correctly limits the results to scan only those with a brand id of 5. However it scans every row with a brand_id of 5 no matter what the pub_date is. With a few million rows in this table this gets very slow. Is there anyway to stop it scanning every row for each date and only scan the ones in the date range specified in the query? As thats why there is a key on the pub_date field I thought, to prevent row scans?
Thanks very much in advance
Comment