Query help

MySQL & MariaDB Other MySQL® Questions
1

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

2

Did You try key ( brand_id, pub_date ) ?

What does explain show when You look only for

[B]Quote:[/B]

SELECT title
FROM data
WHERE brand_id = 5 and pub_date = “2008-11-01”

?
3

Thanks,
Do you mean combining the two keys when the index is built? I haven’t tried that, is that likely to be faster than having 2 seperate keys?

4

Oh and it shows

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE data ref brand_id,pub_date_2 pub_date_2 3 const 10780 Using where

5

It should be faster.

6

Will it stop the massive row scans? I don’t quite understand how combining indexes helps? Any resources or explanations people could point me too.
Thanks

7

Mysql is looking for optimal index, brand_id gives more results than pub_date_2, so mysql is using this one. You could combine brand_id and pub_date, that way mysql will use both values and check only those rows which have got right brand_id and right pub_date.

8

Excellent thanks for your help.