GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

select between dates... very slow query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • select between dates... very slow query

    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)
Working...
X