| Optimizing Query [message #3703] |
Fri, 07 November 2008 08:18 |
USAF_Pride
Messages: 1
Registered: November 2008 |
Junior Member |
|
|
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, vtime
FROM 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_id
LEFT JOIN tbl_date ON tbl_player_kick.date_id = tbl_date.date_id
LEFT JOIN tbl_time ON tbl_player_kick.time_id = tbl_time.time_id
WHERE 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.
[Updated on: Fri, 07 November 2008 09:28]
|
|
|
Members
Search
Help
Register
Login
Home
