use of index

BubikolRamios · 01-04-2013, 11:18 AM

Code:

Select *
from galery_1 g left join tezaver t on g.id_tezaver = t.id_tezaver and t.l2 = 'la'

Code:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","g","ALL",(Null),(Null)(Null)(Null),"8099",""
"1","SIMPLE","t","ref","id_l2,Index_3","id_l2","16","test.g.id_tezaver,const","1",""

If I remove 'left' from query

Code:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","g","ALL","Index_2",\N,\N,\N,"8099",""
"1","SIMPLE","t","ref","id_l2,Index_3","id_l2","16","test.g.id_tezaver,const","1","Using where"

So I get possible index, which is not used.

Code:

Index_2(id_tezaver)
 id_l2(id_tezaver,la)

Any suggestion ?

Furthermore, if I use 'FORCE INDEX (index_2)' or 'USE INDEX (index_2), should that reflect in EXPLAIN result, coz it looks like it don't ?

Old Pedant · 01-04-2013, 07:46 PM

If MySQL decides that using a key won't help performance, then it will be very stubborn about it. There's not much you can do to force it to use the keys.

With only 8099 rows involved, MySQL undoubtedly feels that just loading the entire table into memory will be better than using the index.

BubikolRamios · 01-04-2013, 08:14 PM

Thanks.