Mary, 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 https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
