# Query help

**URL:** <https://forums.percona.com/t/query-help/1050>\
**Category:** Other MySQL® Questions\
**Created:** [January 6, 2009, 12:13pm UTC](https://forums.percona.com/t/query-help/1050 "2009-01-06T12:13:01Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![leonch](https://avatars.discourse-cdn.com/v4/letter/l/ac8455/32.png) [@leonch](https://forums.percona.com/u/leonch)\
**Post date:** [January 6, 2009, 12:13pm UTC](https://forums.percona.com/t/query-help/1050/1 "2009-01-06T12:13:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [January 6, 2009, 3:04pm UTC](https://forums.percona.com/t/query-help/1050/2 "2009-01-06T15:04:07Z")

</div>

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”

 |

 ?

---

<div class="post-metadata">

**Author:** ![leonch](https://avatars.discourse-cdn.com/v4/letter/l/ac8455/32.png) [@leonch](https://forums.percona.com/u/leonch)\
**Post date:** [January 6, 2009, 3:15pm UTC](https://forums.percona.com/t/query-help/1050/3 "2009-01-06T15:15:39Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![leonch](https://avatars.discourse-cdn.com/v4/letter/l/ac8455/32.png) [@leonch](https://forums.percona.com/u/leonch)\
**Post date:** [January 6, 2009, 3:17pm UTC](https://forums.percona.com/t/query-help/1050/4 "2009-01-06T15:17:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [January 6, 2009, 3:21pm UTC](https://forums.percona.com/t/query-help/1050/5 "2009-01-06T15:21:13Z")

</div>

It should be faster.

---

<div class="post-metadata">

**Author:** ![leonch](https://avatars.discourse-cdn.com/v4/letter/l/ac8455/32.png) [@leonch](https://forums.percona.com/u/leonch)\
**Post date:** [January 6, 2009, 3:40pm UTC](https://forums.percona.com/t/query-help/1050/6 "2009-01-06T15:40:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [January 6, 2009, 3:43pm UTC](https://forums.percona.com/t/query-help/1050/7 "2009-01-06T15:43:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![leonch](https://avatars.discourse-cdn.com/v4/letter/l/ac8455/32.png) [@leonch](https://forums.percona.com/u/leonch)\
**Post date:** [January 6, 2009, 3:45pm UTC](https://forums.percona.com/t/query-help/1050/8 "2009-01-06T15:45:02Z")

</div>

Excellent thanks for your help.