# Query optimization with ORDER BY IF clause

**URL:** <https://forums.percona.com/t/query-optimization-with-order-by-if-clause/732>\
**Category:** Other MySQL® Questions\
**Created:** [April 25, 2008, 1:46am UTC](https://forums.percona.com/t/query-optimization-with-order-by-if-clause/732 "2008-04-25T01:46:38Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![slabbi](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@slabbi](https://forums.percona.com/u/slabbi)\
**Post date:** [April 25, 2008, 1:46am UTC](https://forums.percona.com/t/query-optimization-with-order-by-if-clause/732/1 "2008-04-25T01:46:38Z")

</div>

Hi,

I am using following query:

SELECT a.\*, ufrom.name AS fromname, uto.name AS tonameFROM (jos\_uddeim AS a LEFT JOIN jos\_users AS ufrom ON a.fromid = ufrom.id)LEFT JOIN jos\_users AS uto ON a.toid = uto.idWHERE (a.totrashdate \>= 123213213 AND a.totrash=1 AND a.toid = 65) OR(a.totrashdateoutbox \>= 123213213 AND a.totrashoutbox=1 AND a.fromid=65 AND a.toid\<\>a.fromid AND ((a.systemmessage IS NULL) OR (a.systemmessage=‘’)))ORDER BY IF(totrashdate,totrashdate,totrashdateoutbox) DESC

The table structure is

CREATE TABLE IF NOT EXISTS `jos_uddeim` ( `id` int(10) unsigned NOT NULL auto\_increment, `fromid` int(11) NOT NULL default ‘0’, `toid` int(11) NOT NULL default ‘0’, `message` text collate latin1\_german1\_ci NOT NULL, `datum` int(11) default NULL, `toread` int(1) NOT NULL default ‘0’, `totrash` int(1) NOT NULL default ‘0’, `totrashdate` int(11) default NULL, `expires` int(11) default NULL, `disablereply` int(1) NOT NULL default ‘0’, `systemmessage` varchar(60) collate latin1\_german1\_ci default NULL, `archived` int(1) NOT NULL default ‘0’, `totrashoutbox` int(1) NOT NULL default ‘0’, `totrashdateoutbox` int(11) default NULL, `cryptmode` int(1) NOT NULL default ‘0’, `crypthash` varchar(32) collate latin1\_german1\_ci default NULL, `publicname` text collate latin1\_german1\_ci, `publicemail` text collate latin1\_german1\_ci, PRIMARY KEY (`id`), KEY `toid_toread` (`toid`,`toread`), KEY `datum` (`datum`), KEY `totrashdate` (`totrashdate`), KEY `totrashdateoutbox` (`totrashdateoutbox`), KEY `toread_totrash_datum` (`toread`,`totrash`,`datum`), KEY `totrash_totrashdate` (`totrash`,`totrashdate`), KEY `fromid` (`fromid`)) ENGINE=MyISAM;

d select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE a ALL toid\_toread,totrashdate,totrashdateoutbox,totrash\_… NULL NULL NULL 2852 Using where; Using filesort1 SIMPLE ufrom eq\_ref PRIMARY PRIMARY 4 web.a.fromid 1 1 SIMPLE uto eq\_ref PRIMARY PRIMARY 4 web.a.toid 1

Is there a way to optimize the query, so “filesort” is not used? Why does it show “using where”? I tried to index “systemmessage” which was the only field not indexed so far, but still the same message.

I already tried to move the IF cluse in the SELECT part “IF(…) AS thedate,” and used “ORDERED BY thedate”. But also this has not speed up anything.