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:[EMAIL PROTECTED]]
Sent: Wednesday, August 28, 2002 11:11 AM
To: Mary Stickney
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ 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
