GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimizing Query

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

  • Optimizing Query

    I have a database with 9 tables of unique data and 1 table that links it all together. I know the speed issue is all the JOINS and I am way outside of my league in attempting to fix it. The one table that links everything together only stores the primary ID from the other 9 tables. Here is the sql

    SELECT DISTINCT guid, name as unique_name, ip as unique_IP, Violation, vdate, vtimeFROM tbl_player_kick LEFT JOIN tbl_guid ON tbl_player_kick.guid_id = tbl_guid.guid_id LEFT JOIN tbl_kick ON tbl_player_kick.kick_id = tbl_kick.kick_id LEFT JOIN tbl_name ON tbl_player_kick.name_id = tbl_name.name_id LEFT JOIN tbl_ip ON tbl_player_kick.ip_id = tbl_ip.ip_id LEFT JOIN tbl_violation ON tbl_player_kick.viol_type = tbl_violation.violation_idLEFT JOIN tbl_date ON tbl_player_kick.date_id = tbl_date.date_idLEFT JOIN tbl_time ON tbl_player_kick.time_id = tbl_time.time_idWHERE tbl_player_kick.banned = 1


    and the explain that goes with it

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE tbl_player_kick ref banned banned 2 const 262650 Using where; Using temporary
    1 SIMPLE tbl_guid eq_ref PRIMARY PRIMARY 4 aaser2_bansdbase.tbl_player_kick.guid_id 1
    1 SIMPLE tbl_kick eq_ref PRIMARY PRIMARY 8 aaser2_bansdbase.tbl_player_kick.kick_id 1 Using index
    1 SIMPLE tbl_name eq_ref PRIMARY PRIMARY 4 aaser2_bansdbase.tbl_player_kick.name_id 1
    1 SIMPLE tbl_ip eq_ref PRIMARY PRIMARY 4 aaser2_bansdbase.tbl_player_kick.ip_id 1
    1 SIMPLE tbl_violation eq_ref PRIMARY PRIMARY 8 aaser2_bansdbase.tbl_player_kick.viol_type 1
    1 SIMPLE tbl_date eq_ref PRIMARY PRIMARY 4 aaser2_bansdbase.tbl_player_kick.date_id 1
    1 SIMPLE tbl_time eq_ref PRIMARY PRIMARY 8 aaser2_bansdbase.tbl_player_kick.time_id 1

    Any help would be greatly appreciated.

  • #2
    I don't see evidence that the joins are the problem. You have all eq_ref which should be fine. I would guess that the problem is "using temporary" which is caused by DISTINCT.

    Comment

    Working...
    X