# select between dates... very slow query

**URL:** <https://forums.percona.com/t/select-between-dates-very-slow-query/531>\
**Category:** Other MySQL® Questions\
**Created:** [November 19, 2007, 10:14am UTC](https://forums.percona.com/t/select-between-dates-very-slow-query/531 "2007-11-19T10:14:42Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![spak](https://avatars.discourse-cdn.com/v4/letter/s/34f0e0/32.png) [@spak](https://forums.percona.com/u/spak)\
**Post date:** [November 19, 2007, 10:14am UTC](https://forums.percona.com/t/select-between-dates-very-slow-query/531/1 "2007-11-19T10:14:42Z")

</div>

I’m having problem with this query. It shows up in the slow query log file but I’m not sure whether it’s badly indexed or if the query should be written in a different manner.

QUERY:

SELECT stocks\_inhand.holder\_id as hid, people.person\_firstname AS firstname, people.person\_lastname AS lastname, employees.employee\_ref AS empref, (SELECT inhand\_class\_1 FROM stocks\_inhand WHERE id = (SELECT MAX(id) FROM stocks\_inhand WHERE date \<= ‘2006-01-01’ AND holder\_id = hid)) AS inhand\_from, (SELECT inhand\_class\_1 FROM stocks\_inhand WHERE id = (SELECT MAX(id) FROM stocks\_inhand WHERE date \<= ‘2006-12-31’ AND holder\_id = hid)) AS inhand\_toFROM peopleLEFT JOIN employeesON people.person\_id = employees.person\_idLEFT JOIN stocks\_holdersON people.person\_id = stocks\_holders.person\_idLEFT JOIN stocks\_inhandON stocks\_holders.holder\_id = stocks\_inhand.holder\_idWHERE stocks\_inhand.holder\_id IS NOT NULLGROUP BY stocks\_inhand.holder\_idLIMIT 0,300

OUTPUT FROM EXPLAIN:  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: stocks\_holders  
type: ALL  
possible\_keys: PRIMARY,person\_id  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: 990  
Extra: Using where; Using temporary; Using filesort  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: people  
type: eq\_ref  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: norconsult.stocks\_holders.person\_id  
rows: 1  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: employees  
type: ref  
possible\_keys: person\_id  
key: person\_id  
key\_len: 4  
ref: norconsult.people.person\_id  
rows: 1  
Extra:  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: stocks\_inhand  
type: ref  
possible\_keys: holder\_id  
key: holder\_id  
key\_len: 4  
ref: norconsult.stocks\_holders.holder\_id  
rows: 17  
Extra: Using where; Using index  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 4  
select\_type: DEPENDENT SUBQUERY  
table: stocks\_inhand  
type: eq\_ref  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: func  
rows: 1  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 6. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 5  
select\_type: DEPENDENT SUBQUERY  
table: stocks\_inhand  
type: ref  
possible\_keys: holder\_id,date  
key: holder\_id  
key\_len: 4  
ref: func  
rows: 17  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 7. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 2  
select\_type: DEPENDENT SUBQUERY  
table: stocks\_inhand  
type: eq\_ref  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: func  
rows: 1  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 8. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 3  
select\_type: DEPENDENT SUBQUERY  
table: stocks\_inhand  
type: ref  
possible\_keys: holder\_id,date  
key: holder\_id  
key\_len: 4  
ref: func  
rows: 17  
Extra: Using where  
8 rows in set (0.00 sec)