Mysql EXPLAIN Using temporary, Using filesort

Databases
1

Hi

I’m trying to optimize my query and DB using the EXPLAIN. i have read that having the Extra: Using temporary, Using filesort, was sign of a need to optimize queries.

I have those every time i have an ORDER BY in my query

the query i run:

EXPLAIN SELECT t.item_id, n.label, n.country_name
FROM country_info AS n
LEFT JOIN country_item AS t ON n.country = t.item_id
WHERE n.lang =1
AND t.continent =3
ORDER BY n.label ASC

i tried to put indexes on the “label” field. but does nothing. The only way is to remove the ORDER BY

how can i prevent this while being able to order my results?

Thanks

2

please post the output of explain.

3

i have attached a screeshot to make it clearer.

There it is:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref PRIMARY,continent continent 4 const 16 Using where; Using temporary; Using filesort
1 SIMPLE n ref country,lang country 4 passeport.t.item_id 2 Using where

4

if the number of rows that mysql has to search through is greater than 50% or so, it will always “filesort” because it’s faster to sequentially scan the whole table than to access 50% of the index nodes one at a time.

how many rows are in the t table?

5

Thanks for the info

The table “t” contains 171 entries

And also i have found this info on MySql about the indexes and the ORDER BY.