Mysql join table order by index

First Master Table

CREATE TABLE `edipostingmaster` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `checkDate` date DEFAULT NULL,
  `checkCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unqiue` (`checkNo`,`fileName`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

First Detail Table

CREATE TABLE `edipostingclaims` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) NOT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `claimExists` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UNIQUE` (`fileName`,`checkNo`,`claimSno`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8

Second detail table

CREATE TABLE `edipostingcodes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) DEFAULT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `lineItemNo` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `unique` (`checkNo`,`claimSno`,`fileName`,`lineItemNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1289 DEFAULT CHARSET=utf8

Here is the query

EXPLAIN
SELECT * FROM edipostingmaster a  , edipostingclaims b, edipostingclaims c
WHERE a.fileName = b.fileName AND a.checkNo = b.checkNo
AND b.fileName = c.fileName AND b.checkNo = c.checkNo AND b.claimSno = c.claimSno
AND a.checkNo ='893881996'
ORDER BY b.claimSno

Now explain showing Using where; Using temporary; Using filesort for table a.
Without order by, it is fine.

In this case, while an index could be used (not in the current state, but if the index changed its column order a bit) to speed up the ordering, the optimizer has to decide between favoring the filtering or the ordering.

As it probably starts by filtering by the constant a.checkNo ='893881996', and accessing first the table a, it would be impossible to get the records in b.claimSno order (even with different indexes).

As such, a sorting process is required at the end of the row retrieval, and that requires a temporary table. Do not be too worried unless the difference in time between each execution is very high.

If the table ends up being created on disk, you can try tuning tmp_table_size and max_heap_table_size for that session to try to avoid that - although with the couple of varchar(255) that you have, it probably will be worse, not better. If the number of sort_merge_passes is very high, you can try tuning the sort_buffer_size for that session.

On the other hand, if the query is running fast enough for you, you do not need to worry as it is something normal.

StackExchange Database Administrators Q#73055, answer score: 2