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)