Announcement

Announcement Module
Collapse
No announcement yet.

Query optimization with ORDER BY IF clause

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query optimization with ORDER BY IF clause

    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.
Working...
X