MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 11 2004 5:36pm
Subject:RE: Help with query performance anomaly
View as plain text  
Response at end....

"Graham Cossey" <graham@stripped> 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 difference in key definitions?
> >
> > Can 2 installations with the same table definitions produce different
> > results like this? Maybe something in the configs?
> >
> [snip]
> > >
> > >
> > > 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.)
> > >
> [snip]
> > > >
> > > > Can someone offer any advice on a strange problem I have at 
present...
> > > >
> > > > If I run a certain query (see below) on my local development PC 
using
> > > > mysqlcc it returns in 3.7s.
> > > >
> > > > If I run the exact same query on my live webserver (again using
> > > mysqlcc)
> > > I
> > > > have yet to get a result !!
> > > >
> > > > Both databases have the same table definitions (live db originally
> > > created
> > > > from mysqldump of dev PC) and have exactly the same [number
> > of] records
> > > in
> > > > each table. Both machines are running MySQL 3.23.58. Dev PC if 
Fedora
> > > Core2,
> > > > live is RedHat 9.
> > > >
> > > > Other than this one query all else appears normal, any 
suggestions?
> > > > Let me know if you need more info and I'll attempt to supply it...
> > > >
> > > > Many thanks
> > > >
> > > > Graham
> > > >
> [snip]
> 
> I've done mysqldumps of the tables involved on both machines and the 
create
> table definitions and key definitions are identical.
> 
> The results of my EXPLAINs are pasted below.
> 
> Thanks
> Graham
> 
> DEV BOX:
> 
> EXPLAIN SELECT d.dcode, sum(qty) as total
> FROM table1 as d, db2.table2 as r, table3 as p
> WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode 
and
> from_period <= 200410 and to_period > 200410 and d.region!='6'
> GROUP BY dcode
> 
> 
+-------+-------+-----------------------------------------------------------
> 
-----+----------+---------+---------------------+------+--------------------
> --------------------------+
> | table | type  | possible_keys
> | key      | key_len | ref                 | rows | Extra
> |
> 
+-------+-------+-----------------------------------------------------------
> 
-----+----------+---------+---------------------+------+--------------------
> --------------------------+
> | d     | ALL   | [NULL]
> | [NULL]   |  [NULL] | [NULL]              |  322 | Using where; Using
> temporary; Using filesort |
> | p     | index | PRIMARY
> | PRIMARY  |      19 | [NULL]              | 6082 | Using where; Using 
index
> |
> | r     | ref   |
> PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | 
yr_mn_pc |
> 13 | const,const,p.pcode |   41 | Using where
> |
> 
+-------+-------+-----------------------------------------------------------
> 
-----+----------+---------+---------------------+------+--------------------
> --------------------------+
> 
> LIVE SERVER:
> 
> EXPLAIN SELECT d.dcode, sum(qty) as total
> FROM table1 as d, db2.table2 as r, table3 as p
> WHERE year=2004  AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode 
and
> from_period <= 200410 and to_period > 200410 and d.region!='6'
> GROUP BY dcode
> 
> 
+-------+------+------------------------------------------------------------
> 
----+---------+---------+-------------+-------+-----------------------------
> -----------------+
> | table | type | possible_keys
> | key     | key_len | ref         | rows  | Extra
> |
> 
+-------+------+------------------------------------------------------------
> 
----+---------+---------+-------------+-------+-----------------------------
> -----------------+
> | d     | ALL  | [NULL]
> | [NULL]  |  [NULL] | [NULL]      |   322 | Using where; Using 
temporary;
> Using filesort |
> | r     | ref  |
> PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st  | PRIMARY 
|
> 8 | const,const | 89618 | Using where                                  |
> | p     | ref  | PRIMARY
> | PRIMARY |       4 | r.pcode     |     2 | Using where; Using index
> |
> 
+-------+------+------------------------------------------------------------
> 
----+---------+---------+-------------+-------+-----------------------------
> -----------------+
> 
> 

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 a previous message works 
out to help the statistics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Help with query performance anomalyGraham Cossey11 Nov
  • Re: Help with query performance anomalySGreen11 Nov
    • RE: Help with query performance anomalyGraham Cossey11 Nov
      • RE: Help with query performance anomalySGreen11 Nov
      • RE: Help with query performance anomalyGraham Cossey11 Nov
        • RE: Help with query performance anomalySGreen11 Nov
          • RE: Help with query performance anomalyGraham Cossey11 Nov
            • Re: Help with query performance anomalyJamie Kinney11 Nov
              • RE: Help with query performance anomalyGraham Cossey11 Nov
                • RE: Help with query performance anomalyGraham Cossey11 Nov
                • RE: Help with query performance anomaly (SOLVED)Graham Cossey12 Nov
RE: Help with query performance anomalySteven Roussey13 Nov
  • RE: Help with query performance anomalyGraham Cossey13 Nov