RE: index problem

[Mary Stickney]

I answered my own question.....

I was not mentioning AdminCoverage_writingagents in the Where clause....
so the index was not being used.

I had to change the query to :

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






-----Original Message-----
From: Mary Stickney [mailto:mary.stickney@stripped]
Sent: Friday, August 23, 2002 11:15 AM
To: Mary Stickney; 'mysql@stripped'
Subject: index problem



sql,query




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)
);





Mary Stickney
TAG-TMI
Data Warehouse / Imaging
402-474-7612 x 3099
Mary.Stickney@stripped