Hello,
Im looking for some advice on my query, I have tried to optimize it as much as possible but i’m wondering if I could lower the query time some more.
SELECT kll.kll_id, kll.kll_timestamp, plt.plt_name, crp.crp_name, ali.all_name, kll.kll_ship_id, kll.kll_system_id, kll.kll_ship_id, kll.kll_victim_id, plt.plt_externalid, kll.kll_crp_id, kll.kll_points, kll.kll_isk, shp.shp_class, shp.shp_name, shp.shp_externalid, scl.scl_id, scl.scl_class, scl.scl_value, sys.sys_name, sys.sys_secFROM mg1_kills kll inner join mg1_ships shp on (shp.shp_id = kll.kll_ship_id) inner join mg1_ship_classes scl on (scl.scl_id = shp.shp_class) inner join mg1_pilots plt on (plt.plt_id = kll.kll_victim_id) inner join mg1_corps crp on (crp.crp_id = kll.kll_crp_id) inner join mg1_alliances ali on (ali.all_id = kll.kll_all_id) inner join mg1_systems sys on (sys.sys_id = kll.kll_system_id) inner join mg1_inv_detail ind on (ind.ind_kll_id = kll.kll_id)WHERE ind.ind_all_id in (3)GROUP BY kll.kll_idORDER BY kll.kll_timestamp DESC--------------------24357 rows fetched (2.344 sec)--------------------
±—±------------±------±-------±----------------------------------------------------------------------±-----------±--------±------------------±------±--------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-------±----------------------------------------------------------------------±-----------±--------±------------------±------±--------------------------------+| 1 | SIMPLE | ind | ref | ind_kll_id,ind_all_id | ind_all_id | 4 | const | 79730 | Using temporary; Using filesort || 1 | SIMPLE | kll | eq_ref | PRIMARY,kll_ship_id,kll_victim_id,kll_all_id,kll_crp_id,kll_system_id | PRIMARY | 4 | ind.ind_kll_id | 1 | || 1 | SIMPLE | shp | eq_ref | PRIMARY,shp_class | PRIMARY | 4 | kll.kll_ship_id | 1 | || 1 | SIMPLE | crp | eq_ref | PRIMARY | PRIMARY | 4 | kll.kll_crp_id | 1 | || 1 | SIMPLE | sys | eq_ref | PRIMARY | PRIMARY | 4 | kll.kll_system_id | 1 | || 1 | SIMPLE | plt | eq_ref | PRIMARY | PRIMARY | 4 | kll.kll_victim_id | 1 | || 1 | SIMPLE | ali | eq_ref | PRIMARY | PRIMARY | 4 | kll.kll_all_id | 1 | || 1 | SIMPLE | scl | eq_ref | PRIMARY | PRIMARY | 4 | shp.shp_class | 1 | |±—±------------±------±-------±----------------------------------------------------------------------±-----------±--------±------------------±------±--------------------------------+
Any help in speeding up this query would be appreciated! Please let me know if you need any other information.
Cheers