Hi, I would add some indicies to the two tables.
Looking at the below I waould add an index on the following fields: files.directory_id files.lang_id files.class_tbl access.conn_id access.group_id access.class_id You can do this easily with the "alter table xxxx add index..." syntax easily. It should run way faster then.. Cheers, Andrew -----Original Message----- From: Eric Persson [mailto:[EMAIL PROTECTED] Sent: Tuesday 01 April 2003 19:02 To: MySQL List Subject: Optimize query, avoid 'using temporary' Hi, I have a query which I did several months ago, it recently caught my attention since it seems to be very slow when done a lot of times, which causes a very loaded server. Its a webbased filesystem, which stores access/file information in mysql, the actual files are stored on disk. The problem is when I want to get all files in a directory which the current user have access to. Below are the table structures used for this(descripten below them): 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; # The table above is used for more the the accessinfo for the files, # its also used for directories etc. Thats why conn_id==file_id in # this case. And class_id=4 and class_tbl=file 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; # # Actual file information, lang_id=1 and directory_id=0 in this case # 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; Since access can have several rows per file_id(associated by file_id=conn_id ) I have to use group by to avoid getting multiple lines of the same file. The part access.group_id IN (1) is the groups which the user have access to, could be more of them to. Used one for simplicity here. An explain of the query gives me: mysql> explain 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\G *************************** 1. row *************************** table: access type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8958 Extra: where used; Using temporary; Using filesort *************************** 2. row *************************** table: files type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: access.conn_id rows: 1 Extra: where used 2 rows in set (0.00 sec) If I exclude the group by and order by parts I get only where used, which is good, but gives me the wrong result. Is it possible to rewrite the query to get better performance out of this? Or do I have to change the table structure? Thanks in advance, best regards, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
