| select between dates... very slow query [message #2220] |
Mon, 19 November 2007 10:44 |
 spak
Messages: 1
Registered: November 2007
Location: Norway |
Junior Member |
|
|
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_to
FROM
people
LEFT JOIN
employees
ON
people.person_id = employees.person_id
LEFT JOIN
stocks_holders
ON
people.person_id = stocks_holders.person_id
LEFT JOIN
stocks_inhand
ON
stocks_holders.holder_id = stocks_inhand.holder_id
WHERE
stocks_inhand.holder_id IS NOT NULL
GROUP BY
stocks_inhand.holder_id
LIMIT 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)
|
|
|
Members
Search
Help
Register
Login
Home
