File Sort
Posted by:
aftab khan
Date: January 25, 2007 08:49AM
the following query used filesort, but the `Prefix` field has an index.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE customerextraction ALL NULL NULL NULL NULL 4993 Using where; Using filesort
EXPLAIN SELECT IF( `CountryCode` = `OperatorList` , length( `Prefix` ) , length( `Prefix` ) + length( `OperatorList` ) ) AS length, `DestinationID` , `CustomerID` , `Prefix` , `OperatorList` , `CountryCode` , ID
FROM customerextraction
WHERE `Prefix` = substring( '2023380', 1, length( `Prefix` ) )
ORDER BY length DESC
-----------------
CREATE TABLE `customerextraction` (\n `CustomerID` bigint(20) NOT NULL,\n `Prefix` varchar(200) collate latin1_general_ci default NULL,\n `OperatorList` varchar(200) collate latin1_general_ci default NULL,\n `DestinationID` varchar(500) collate latin1_general_ci default NULL,\n `CountryCode` varchar(200) collate latin1_general_ci default NULL,\n `ID` bigint(20) NOT NULL auto_increment,\n PRIMARY KEY (`ID`),\n KEY `CustomerID` (`CustomerID`),\n KEY `Prefix` (`Prefix`),\n CONSTRAINT `customerextraction_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci