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.