Jordan Russell wrote:
>
> > 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
Do you have an index on files that starts with dir_id and
an index on dirs that stats with id and having date in the files
index (after dir_id) might be helpful although I'm not sure.
you could provide the output of
show keys from files;
show keys from dirs;
it would be helpful..
---------------------------------------------------------------------
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
