List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:August 16 2002 5:57pm
Subject:Re: improving slow query (was: MySQL vs. Oracle (not speed))
View as plain text  
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
Thread
MySQL vs. Oracle (not speed)Elizabeth Bogner16 Aug
  • Re: MySQL vs. Oracle (not speed)Matthew Walker16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
    • RE: MySQL vs. Oracle (not speed)Kent Vilhelmsen16 Aug
    • RE: MySQL vs. Oracle (not speed)Francisco16 Aug
      • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
              • Re: MySQL vs. Oracle (not speed)Serge Paquin16 Aug
                • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                  • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
                • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                  • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                    • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                      • Re: MySQL vs. Oracle (not speed)Van16 Aug
                        • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • Re: MySQL vs. Oracle (not speed)Dan Nelson16 Aug
                        • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
        • RE: MySQL vs. Oracle (not speed)Francisco16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                    • Re: improving slow query (was: MySQL vs. Oracle (not speed))Benjamin Pflugmann16 Aug
                • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
            • RE: MySQL vs. Oracle (not speed) - not part of the rant, but real informationAdam Nelson16 Aug
              • Re: MySQL vs. Oracle (not speed) - not part of the rant, but real informationDavid Lloyd17 Aug
        • Re: MySQL vs. Oracle (not speed)Tom Gao16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
    • Re: MySQL vs. Oracle (not speed)Gelu Gogancea16 Aug
      • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • Re: MySQL vs. Oracle (not speed)Gelu Gogancea16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
  • Re: MySQL vs. Oracle (not speed)Anderson Pereira Ataides16 Aug
RE: MySQL vs. Oracle (not speed)Art Fore16 Aug
RE: MySQL vs. Oracle (not speed)John Griffin16 Aug
  • Re: MySQL vs. Oracle (not speed)Elizabeth Bogner17 Aug
Re: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug
RE: MySQL vs. Oracle (not speed)Alec.Cawley16 Aug
RE: MySQL vs. Oracle (not speed)John Griffin16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
Re: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug
RE: MySQL vs. Oracle (not speed)Kenneth Hylton16 Aug
Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney17 Aug
RE: MySQL vs. Oracle (not speed)Steve Orr16 Aug
RE: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug