MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mary Stickney Date:August 28 2002 4:49pm
Subject:RE: MS Access and mySQL
View as plain text  
see my message re--- index problem

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


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


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


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




-----Original Message-----
From: Mark Matthews [mailto:mark@stripped]
Sent: Wednesday, August 28, 2002 11:11 AM
To: Mary Stickney
Cc: nick@stripped; mysql@stripped;
jonathanrcoleman@stripped
Subject: Re: MS Access and mySQL


Mary Stickney wrote:
> the join syntax is what is differnt between Access and MYSQL.
>
> in MS-SQL =  indexs will be used on tables mentioned in the JOIN clause
> and they will not in MYSQL

This is not true. MySQL uses indexes in joins, if you've put them in the
right columns, just as in MS-SQL.

What gives you the idea that it doesn't?

	-Mark


--
For technical support contracts, visit https://order.mysql.com/?ref=mmma

     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
  /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
         <___/ www.mysql.com

Thread
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