Thanks for the advice Steven, I'll bear it in mind and do some reading.
Graham
> -Original Message-
> From: Steven Roussey [mailto:[EMAIL PROTECTED]
> Sent: 13 November 2004 02:52
> To: 'Graham Cossey'
> Cc: [EMAIL PROTECTED]
> Subject: RE: Help with qu
For production systems, I would never let the mysql optimizer guess a query
plan when there are joins of big tables and you know exactly how it should
behave. Once you think a query is finished, you should optimize it yourself.
Use STRAIGHT_JOIN and USE INDEX as found here in the manual:
http://de
It turns out that it appears to be a data discrepancy that caused the query
optimiser to, well, not optimise.
I thought the main table (r) with 3million records would be the problem, but
it was table p with 3100 records on the live server and 3082 records on my
dev pc that caused the problem. Alt
[snip]
>
> Have just run 'top' on the live server...
>
> Before running the query I get:
>
> 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44
> 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
> CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0%
>
^ Does not look good to me !!
Comments?
Advice?
Thanks
Graham
> -Original Message-
> From: Jamie Kinney [mailto:[EMAIL PROTECTED]
> Sent: 11 November 2004 19:25
> To: Graham Cossey
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Help with query performance ano
How do the OS statistics look on both boxes. Do top, sar, vmstat or
iostat show any CPU, memory or I/O performance issues? Does anything
odd appear in the /var/log/messages file?
-Jamie
On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
<[EMAIL PROTECTED]> wrote:
>
>
> [big snip]
>
> >
> > Th
[big snip]
>
> These are two different plans. Your development machine is using
> the index
> yr_mn_pc on the r table and is joining that table last. On your
> production
> server, the r table is joined second and is joined by the index PRIMARY.
> Let me know how the ANALYZE TABLE I suggested in
Response at end
"Graham Cossey" <[EMAIL PROTECTED]> wrote on 11/11/2004 12:19:17 PM:
> > Thanks Shaun
> >
> > EXPLAIN shows the same 'possible keys' for each table but 'key' and
> > 'key-len' columns are different, as are the 'rows' as well of course.
> >
> > I guess this points to a probable
> Thanks Shaun
>
> EXPLAIN shows the same 'possible keys' for each table but 'key' and
> 'key-len' columns are different, as are the 'rows' as well of course.
>
> I guess this points to a probable difference in key definitions?
>
> Can 2 installations with the same table definitions produce differe
ke this? Maybe something in the configs?
>
> Thanks
>
> Graham
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: 11 November 2004 16:28
> > To: Graham Cossey
> > Cc: [EMAIL PROTECTED]
> > Subject: Re
le definitions produce different
results like this? Maybe something in the configs?
Thanks
Graham
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 11 November 2004 16:28
> To: Graham Cossey
> Cc: [EMAIL PROTECTED]
> Subject: Re: Help with
What does EXPLAIN show for the query on both systems? (I am wondering if
you may have an index on your development system that you do not have on
your production server.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Graham Cossey" <[EMAIL PROTECTED]> wrote on 11/11/200
12 matches
Mail list logo
