Re: improving slow query (was: MySQL vs. Oracle (not speed))

[Benjamin Pflugmann]

Hello.

On Fri 2002-08-16 at 10:49:00 -0500, mary.stickney@stripped wrote:
> 
> yes I do....

But they are not listed in the "Possible Keys" column, which they
should. MySQL cannot use them for some reason.

I had a look at the posting with the CREATE TABLEs and it seems the
reason is that the columns are not declared identically in the two
tables (taxid allows NULL in one and it NOT NULL in the other).

Please go through all indexed fields and assure they are the same as
their corresponding fields in all tables. Then post again the results
of DESC <tables> and EXPLAIN (and also the SELECT statement itself,
for reference). For EXPLAIN, please make use \G in the command line
client, which will format the output column-wise instead of row-wise,
which is better readable. Thanks.

Regards,

	Benjamin.


PS: Subject changed to reflect new topic


[...]
> Do you have an index on AdminProducer.taxid and on tempsap.taxid ?
[...]
> > explian returns this
> >
> > ;table;type;possible_keys;key;key_len;ref;rows;Extra
> > ;table;type;possible_keys;key;key_len;ref;rows;Extra
> >
> > ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb
> > cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used;
> > Using temporary; Using filesort
> >
> > ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co
> >
> > vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w
> > here used
> >
> > ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing
> > AgentID;11;
> >
> > ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1
> > 1;
> > ;tempsap;ALL;;;;;10019;
> > >
[...]

-- 
benjamin-mysql@stripped