to slow query

[memfiss]

this query without "ORDER BY" turn in 4s. , with  "ORDER BY" about 30s.

SELECT 
  TRIM(LEFT(`ResignLogin`.`ResignReason`, 50)) AS `Reason`,
  `ResignLogin`.`ResignDate`,
  `ResignLogin`.`CountryID`,
  `Login`.`LangID` ,
  `ResignStatus`.`Status`
   FROM 
        `ResignLogin` 
         LEFT JOIN `Login` ON `ResignLogin`.`LoginID` = `Login`.`LoginID` 
         LEFT JOIN `ResignStatus` ON `ResignLogin`.`LoginID` = `ResignStatus`.`LoginID`
   ORDER BY `ResignLogin`.`ResignDate` DESC
   LIMIT 10;

explain :

+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+--------+---------------------+
| id | select_type | TABLE        | TYPE   | possible_keys | KEY     | key_len | REF                         | ROWS   | Extra               |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+--------+---------------------+
|  1 | SIMPLE      | ResignStatus | system | PRIMARY       | NULL    | NULL    | NULL                        |      0 | const ROW NOT found |
|  1 | SIMPLE      | ResignLogin  | ALL    | NULL          | NULL    | NULL    | NULL                        | 401369 | USING filesort      |
|  1 | SIMPLE      | Login        | eq_ref | PRIMARY       | PRIMARY | 30      | ABSOLUX.ResignLogin.LoginID |      1 |                     |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+--------+---------------------+

how can i make it faster ? tnx.

[Jessica]

Add an index?

[DavidAM]

With the ORDER BY the server has to sort all 400,000+ rows in the table to determine which 10 to send back. Without the ORDER BY it can send back any 10 records it finds.

 

You can add an index on the ResignDate so the server will already have a sorted list of values to consult. You might need to make it a descending index, I'm not sure.

 

Also, do not use LEFT JOIN unless you really need it. A straight JOIN will perform better.

 

[jazzman1]

The type: ALL of a ResignLogin table is not good, where you joined this table with 401369 rows inside of it..

 

Try to index `LoginID` and `ResignDate` as David and Jesi mentioned above.

 

ALTER TABLE `ResignLogin` ADD INDEX index_on_name (`LoginID`,`ResignDate`)

 

Also, do not use LEFT JOIN unless you really need it. A straight JOIN will perform better.

[fenway]

If you're joining, you'll need at least one index per joined table.