MySQL Order By Issue on large table

I have the following mysql command:

SELECT e.sIndex01 
FROM e_entity e 
WHERE e.meta_oid=336799 
ORDER BY e.sIndex02
LIMIT 100 OFFSET 0

This is the table:

CREATE TABLE `e_entity` (
  `OID` int(11) NOT NULL AUTO_INCREMENT,
  `E_E_OID` int(11) DEFAULT NULL,
  `UNIQUE_IDX` int(11) NOT NULL,
  `APP_OID` int(11) NOT NULL,
  `META_OID` int(11) NOT NULL,
  `STORE_DATE` datetime NOT NULL,
  `REL_DISPLAY` varchar(1024) NOT NULL,
  `SINDEX01` varchar(1024) NOT NULL,
  `SINDEX02` varchar(1024) NOT NULL,
  `SINDEX03` varchar(1024) NOT NULL,
  `SINDEX04` varchar(1024) NOT NULL,
  `SINDEX05` varchar(1024) NOT NULL,
  `SINDEX06` varchar(1024) NOT NULL,
  `SINDEX07` varchar(1024) NOT NULL,
  `SINDEX08` varchar(1024) NOT NULL,
  `SINDEX09` varchar(1024) NOT NULL,
  `SINDEX10` varchar(1024) NOT NULL,
  `SINDEX11` varchar(1024) NOT NULL,
  `SINDEX12` varchar(1024) NOT NULL,
  `SINDEX13` varchar(1024) NOT NULL,
  `SINDEX14` varchar(1024) NOT NULL,
  `SINDEX15` varchar(1024) NOT NULL,
  `SINDEX16` varchar(1024) NOT NULL,
  `SINDEX17` varchar(1024) NOT NULL,
  `SINDEX18` varchar(1024) NOT NULL,
  `SINDEX19` varchar(1024) NOT NULL,
  `SINDEX20` varchar(1024) NOT NULL,
  `NINDEX01` double NOT NULL,
  `NINDEX02` double NOT NULL,
  `NINDEX03` double NOT NULL,
  `NINDEX04` double NOT NULL,
  `NINDEX05` double NOT NULL,
  `NINDEX06` double NOT NULL,
  `NINDEX07` double NOT NULL,
  `NINDEX08` double NOT NULL,
  `NINDEX09` double NOT NULL,
  `NINDEX10` double NOT NULL,
  `DINDEX01` datetime NOT NULL,
  `DINDEX02` datetime NOT NULL,
  `DINDEX03` datetime NOT NULL,
  `DINDEX04` datetime NOT NULL,
  `DINDEX05` datetime NOT NULL,
  `DINDEX06` datetime NOT NULL,
  `DINDEX07` datetime NOT NULL,
  `DINDEX08` datetime NOT NULL,
  `DINDEX09` datetime NOT NULL,
  `DINDEX10` datetime NOT NULL,
  `FREETEXT` mediumtext NOT NULL,
  `UID` int(11) DEFAULT NULL,
  PRIMARY KEY (`OID`),
  KEY `App_Parent` (`META_OID`),
  KEY `sindex01` (`META_OID`,`SINDEX01`(64)),
  KEY `sindex02` (`META_OID`,`SINDEX02`(64)),
  KEY `sindex03` (`META_OID`,`SINDEX03`(64)),
  KEY `sindex04` (`META_OID`,`SINDEX04`(64)),
  KEY `sindex05` (`META_OID`,`SINDEX05`(64)),
  KEY `sindex06` (`META_OID`,`SINDEX06`(64)),
  KEY `sindex07` (`META_OID`,`SINDEX07`(64)),
  KEY `sindex08` (`META_OID`,`SINDEX08`(64)),
  KEY `sindex09` (`META_OID`,`SINDEX09`(64)),
  KEY `sindex10` (`META_OID`,`SINDEX10`(64)),
  KEY `nindex01` (`META_OID`,`NINDEX01`),
  KEY `nindex02` (`META_OID`,`NINDEX02`),
  KEY `nindex03` (`META_OID`,`NINDEX03`),
  KEY `nindex04` (`META_OID`,`NINDEX04`),
  KEY `nindex05` (`META_OID`,`NINDEX05`),
  KEY `dindex01` (`META_OID`,`DINDEX01`),
  KEY `dindex02` (`META_OID`,`DINDEX02`),
  KEY `dindex03` (`META_OID`,`DINDEX03`),
  KEY `dindex04` (`META_OID`,`DINDEX04`),
  KEY `dindex05` (`META_OID`,`DINDEX05`),
  KEY `sindex11` (`META_OID`,`SINDEX11`(64)),
  KEY `sindex12` (`META_OID`,`SINDEX12`(64)),
  KEY `sindex13` (`META_OID`,`SINDEX13`(64)),
  KEY `sindex14` (`META_OID`,`SINDEX14`(64)),
  KEY `sindex15` (`META_OID`,`SINDEX15`(64)),
  KEY `sindex16` (`META_OID`,`SINDEX16`(64)),
  KEY `sindex17` (`META_OID`,`SINDEX17`(64)),
  KEY `sindex18` (`META_OID`,`SINDEX18`(64)),
  KEY `sindex19` (`META_OID`,`SINDEX19`(64)),
  KEY `sindex20` (`META_OID`,`SINDEX20`(64)),
  KEY `nindex06` (`META_OID`,`NINDEX06`),
  KEY `nindex07` (`META_OID`,`NINDEX07`),
  KEY `nindex08` (`META_OID`,`NINDEX08`),
  KEY `nindex09` (`META_OID`,`NINDEX09`),
  KEY `nindex10` (`META_OID`,`NINDEX10`),
  KEY `dindex06` (`META_OID`,`DINDEX06`),
  KEY `dindex07` (`META_OID`,`DINDEX07`),
  KEY `dindex08` (`META_OID`,`DINDEX08`),
  KEY `dindex09` (`META_OID`,`DINDEX09`),
  KEY `dindex10` (`META_OID`,`DINDEX10`),
  KEY `E_E_OID` (`E_E_OID`)
) ENGINE=InnoDB AUTO_INCREMENT=469158 DEFAULT CHARSET=utf8;

The above query takes a couple of minutes to complete, however without the order by clause it takes only 5 seconds, so clearly there's a bottleneck on order by. There are 471000 rows in the table, and the matching result set where I assume order by is executed is 171000 rows. What suggestions can I follow to improve performance?

Solution

MySQL cannot use your prefixed index sIndex02 to order by, as stated in the documentation

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

There is an index on only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if only the first 10 bytes of a CHAR(20) column are indexed, the index cannot distinguish values past the 10th byte and a filesort will be needed.

The filesort will require to read all 171k rows from the table before applying the limit. So to speed up your query, you have to have the whole column in your index to support order by, which is not possible without some slight modifications to your table.

First, enable the configuration option innodb_large_prefix (if you use MySQL < 5.7.7, otherwise it is enabled by default) to increase the key size limit to 3072 bytes.

Then either change your charmap of your SINDEXxx-columns to anything that uses 1 byte per character (e.g. latin1), because utf8 will use (up to) 3 bytes, thus barely exceeding the key size limit, or, if that is not possible because you actually need utf8-characters in that column, slightly reduce your column length to e.g. 1022.

If all of that is not possible (because you need utf8 and a length of 1024), you can add an additional column for each sindexxx with a length of 1022, add a trigger (or a generated column) that stores the first 1022 chars, add an index using META_OID and that new column and order by that one - assuming you can live with not ordering by the last 2 characters. But since you just have 171k rows, the first 1022 characters should hopefully be significant enough.