CREATE TABLE access ( class_id int(10) unsigned NOT NULL default '0', group_id int(10) unsigned NOT NULL default '0', class_tbl char(10) NOT NULL default '', conn_id int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM;
CREATE TABLE files ( file_id int(10) unsigned NOT NULL auto_increment, lang_id int(10) unsigned NOT NULL default '0', directory_id int(10) unsigned NOT NULL default '0', filename varchar(255) NOT NULL default '', PRIMARY KEY (file_id) ) TYPE=MyISAM;
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;
Is it possible to rewrite the query to get better performance out of this? Or do I have to change the table structure?
Try adding a primary key to access(). I'm not sure what combination of columns would be a unique identifier on it, but any index beginning with class_id, class_tbl, and/or group_id (either a primary key or an index) is likely to help significantly. In fact, unless the combination of all three of those fields is a unique identifier, I suggest that you index on that combination.
Indexing "files" on the combination of lang_id and directory_id might help as well.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
