A .03 sec left join PREVENTS you from using MySQL.
This must be a troll.
You have no where clause, so no index is used.
Jordan Russell wrote:
>
> 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
---------------------------------------------------------------------
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
