/* 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;
for grins and giggles, add:
key dir_id(dir_id),
to the above.
Cal
http://www.calevans.com
-----Original Message-----
From: Jordan Russell [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 12, 2001 2:48 PM
To: Cal Evans
Cc: [EMAIL PROTECTED]
Subject: Re: Need help optimizing this (simple) query
> Everything I've seen on this list indicates that using LEFT JOIN negates
> using an index. (WARNING: This is 3rd hand info and as such should be
viewed
> skeptically!)
>
> Have you tried:
>
> SELECT files.id, dirs.name FROM files where files.dir_id=dirs.id
> ORDER BY files.date DESC LIMIT 1;
>
> If so, what were your results?
Thanks.. I had to add ", dirs" for that to work:
SELECT files.id, dirs.name FROM files, dirs where files.dir_id=dirs.id
ORDER BY files.date DESC LIMIT 1;
But unfortunately the results were exactly the same: 0.03 sec query time,
and EXPLAIN SELECT showed the same output as when I used a LEFT JOIN -- no
index, and "using filesort."
I also tried adding "USE INDEX", but still the exact same results:
SELECT files.id, dirs.name FROM files USE INDEX (date), dirs where
files.dir_id=dirs.id ORDER BY files.date DESC LIMIT 1;
And I tried reversing the column/table order; that didn't help either:
SELECT dirs.name, files.id FROM dirs, files where dirs.id=files.dir_id ORDER
BY files.date DESC LIMIT 1;
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
