The query I used looks like this:
SELECT files.file_id, filename FROM access, files WHERE directory_id="0" AND lang_id="1" AND ( files.file_id=access.conn_id AND access.group_id IN (1) AND access.class_id="4" AND class_tbl="file" ) group by file_id order by filename;
Just a short note on your query: If you compare integers in a text context (like lang_id="1"), MySQL cannot use indexes that it could possibly otherwise use. So rewrite those parts (e.g. lang_id=1).
Another advice would be to try a LEFT JOIN of access and files.
And, of course, you could add some indexes, on the filename and the file columns.
I rewrote the column=integer parts but cant really see any speedimprovement there.
I also tried to define a lot of different indexes, but none of them seemed to get rid of the "using temporary" in the explaincommand.
The left join idea sounds reasonable, i tried that a few days ago when I started debugging this, but I didnt get any better result, but it might be worth another look, I'll get back about that.
Should sample data and expected resultlist be helpful for any of you that tried to help me? If I got a nice soloution on this thanks to you, I would be happy to send a bottle of whiskey or something. :)
Thanks, Eric
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
