MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Egor Egorov Date:August 29 2002 11:08am
Subject:Re: RE: MS Access and mySQL
View as plain text  
Wednesday, August 28, 2002, 7:49:22 PM, you wrote:

MS> see my message re--- index problem

MS> ok when I do explain in this , it is not using an index for the
MS> AdminCoverage_writingagents table...

MS> SELECT admincoverage_writingagents.WritingAgentID,
MS> admincoverage_writingagents.WritingAgentSlot, ProducerID, TaxID,
MS>  (ModalPremium * BillModeID * (PercentOfCase / 100)) AS TotalPaidPremium,
MS> AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc,
MS> admincoverage_writingagents.RegionCode, AdminProduct.LobId,
MS> AdminCoverage.StatusID, AdminCoverage.StatusDate,
MS> AdminCoverage.InitialPremiumDate,
MS> AdminCoverage.PaidToDate, GROUPID  FROM admincoverage_writingagents
MS> INNER JOIN AdminCoverage ON
MS> admincoverage_writingagents.CoverageID=AdminCoverage.CoverageID
MS> AND admincoverage_writingagents.CoverageIDSbc=AdminCoverage.CoverageIDSbc
MS> LEFT JOIN AdminProducer ON
MS> AdminProducer.ProducerID=admincoverage_writingagents.WritingAgentID
MS> LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID
MS> WHERE AdminCoverage.InitialPremiumDate >= '20000101' AND
MS> AdminCoverage.InitialPremiumDate <= '20020701'
MS> AND TaxID='003349715' ORDER BY
MS> admincoverage_writingagents.WritingAgentSlot,AdminCoverage.CoverageId,
MS> AdminCoverage.CoverageIdSbc
MS> ,ProducerID

MS> table in question has 900,000 records....

MS> CREATE TABLE admincoverage_writingagents (
MS>         Source char(1) NOT NULL  ,
MS>         CoverageId char(15) NOT NULL  ,
MS>         CoverageIdSbc char(10) NOT NULL  ,
MS>         WritingAgentSlot int(11)   ,
MS>         WritingAgentId char(15) NOT NULL  ,
MS>         PercentOfCase double   ,
MS>         RadDistributionCode char(5)   ,
MS>         RegionCode char(5)   ,
MS>         LobId char(15)    ,
MS>         KEY coverageid (CoverageId),
MS>         KEY coverageidsbc (CoverageIdSbc),
MS>         KEY coverageidandsbc (CoverageId, CoverageIdSbc),
MS>         KEY writingagentid (WritingAgentId),
MS>         KEY writingagentslot (WritingAgentSlot)
MS> );

Mary, could you provide output of EXPLAIN SELECT?
What is the structure of other tables?

For technical support contracts, goto
This email is sponsored by
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   Egor.Egorov@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB /

MS Access and mySQLJonathan Coleman28 Aug
  • RE: MS Access and mySQLBryant Hester28 Aug
    • RE: MS Access and mySQLNicholas Stuart28 Aug
      • RE: MS Access and mySQLRoger Davis28 Aug
        • RE: MS Access and mySQLNicholas Stuart28 Aug
        • Re: MS Access and mySQLGelu Gogancea28 Aug
  • mysql password ( )Mark Stringham28 Aug
    • Re: mysql password ( )Leonardo Javier BelĂ©n28 Aug
    • Re: mysql password ( )Daniel Kiss29 Aug
  • Re: mysql password ( )Randy Johnson29 Aug
Re: MS Access and mySQLNicholas Stuart28 Aug
  • RE: MS Access and mySQLMary Stickney28 Aug
    • Re: MS Access and mySQLMark Matthews28 Aug
      • RE: MS Access and mySQLMary Stickney28 Aug
        • Re: RE: MS Access and mySQLEgor Egorov29 Aug
Re: MS Access and mySQLArthur Fuller28 Aug
  • Re: MS Access and mySQLNicholas Stuart29 Aug
Re: mysql password ( )Daniel Kiss29 Aug