Hi everyone,
I'm stuck on one issue which is preventing me from using MySQL in production
on one particular database. (This is similar to my last post but this time
I'll explain better what I'm attempting to do.)
I'm trying to create a database of filenames and directories using MySQL. I
have two tables -- "files" which holds the names & dates of files, and
"dirs" which holds directory names.
Say I want to obtain the ID of the newest file. I execute:
mysql> SELECT id FROM files ORDER BY date DESC LIMIT 1;
+------+
| id |
+------+
| 2736 |
+------+
1 row in set (0.00 sec)
Good so far. Now, I want to do the same thing, but also list the directory
name which the file resides in. So I execute:
mysql> SELECT files.id, dirs.name FROM files
-> LEFT JOIN dirs ON files.dir_id=dirs.id
-> ORDER BY files.date DESC LIMIT 1;
+------+-----------+
| id | name |
+------+-----------+
| 2736 | Folder145 |
+------+-----------+
1 row in set (0.03 sec)
Notice this query takes 0.03 sec to execute vs. 0.00 for the first one...
Why the huge difference in time? It's still a one-row query!
Could someone please explain this analomy? Is it normal for SELECT queries
that include a LEFT JOIN to be *that* slow? Surely it can't be(?). I've read
over the optimization section documentation countless times and I'm still
stumped.
Here are my table specs:
/* This table has 500 rows */
CREATE TABLE dirs (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
/* This table has 5000 rows */
CREATE TABLE files (
id int(11) NOT NULL auto_increment,
dir_id int(11) NOT NULL default '0',
name varchar(100) NOT NULL default '',
date datetime default NULL,
PRIMARY KEY (id),
KEY date (date)
) TYPE=MyISAM;
The output of EXPLAIN SELECT is rather puzzling to me. Why doesn't it use
the "date" key on the second query? It seems like it would be much much
faster if it did.
mysql> EXPLAIN SELECT id FROM files ORDER BY date DESC LIMIT 1;
+-------+-------+---------------+------+---------+------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------+
| files | index | NULL | date | 9 | NULL | 5000 | |
+-------+-------+---------------+------+---------+------+------+-------+
mysql> EXPLAIN SELECT files.id, dirs.name FROM files
-> LEFT JOIN dirs ON files.dir_id=dirs.id
-> ORDER BY files.date DESC LIMIT 1;
+-------+--------+---------------+---------+---------+--------------+------+
----------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+--------------+------+
----------------+
| files | ALL | NULL | NULL | NULL | NULL | 5000 |
Using filesort |
| dirs | eq_ref | PRIMARY | PRIMARY | 4 | files.dir_id | 1 |
|
+-------+--------+---------------+---------+---------+--------------+------+
----------------+
Thanks very much in advance for any assistance.
Jordan Russell
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
