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]
