Hi,
> Ok, I'm out of ideas. The additional index should have sped things up,
not
> slowed them down. I've got some tables with 26,000 rows in it and I do 4
> joins (using a WHERE clause) with smaller tables without a hitch.
>
> Sorry I can't help.
But thanks a lot for trying. :) And thanks als
-Original Message-
From: Jordan Russell [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 12, 2001 3:07 PM
To: Steve Ruby
Cc: Cal Evans; [EMAIL PROTECTED]
Subject: Re: Need help optimizing this (simple) query
> Do you have an index on files that starts with dir_id and
> an index on dirs t
> 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.
Sorry, I don't understand exactly what you mean, but...
> you could provide the output of
> show keys from
> /* 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,
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.i
les, 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
> 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;
>
>
what were your results?
Cal
http://www.calevans.com
-Original Message-
From: Jordan Russell [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 12, 2001 2:27 PM
To: Cal Evans
Cc: [EMAIL PROTECTED]
Subject: Re: Need help optimizing this (simple) query
Hi,
Thanks for the reply.
> Hve you rea
Hi,
Thanks for the reply.
> Hve you read
>
http://www.mysql.com/documentation/mysql/bychapter/manual_Performance.html#L
EFT_JOIN_optimization
> ?
Yes, many times, as well as just about everything else in the Performance
chapter. Am I missing something totally obvious? Should I even be using a
LE
L. Clark
Cc: [EMAIL PROTECTED]
Subject: Re: Need help optimizing this (simple) query
> A .03 sec left join PREVENTS you from using MySQL.
>
> This must be a troll.
Sigh...
I guess I failed to mention this was a deliberately simplified example in
order to pose my question in a easy-to-u
> You have no where clause, so no index is used.
Oops, missed this part.
Where exactly do I need a WHERE clause, and for what? The first query
doesn't have a WHERE clause and yet it appears to be using the "date" index.
Jordan Russell
--
> A .03 sec left join PREVENTS you from using MySQL.
>
> This must be a troll.
Sigh...
I guess I failed to mention this was a deliberately simplified example in
order to pose my question in a easy-to-understand manner. In reality, there
are much more records, and a more complex query, where the t
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
13 matches
Mail list logo
