alter table tableA add index(key1, acct) creates a single index across the two columns. Trying to visualize the index as a sorted list (of course it is a b-tree), it will look something like:
(key, acct:) 1,1 1,2 1,3 1,5 1,8 2,1 2,4 2,5 2,6 3,2 3,9 ... etc. Therefore, this index is not very useful when you are using (only) acct (the second column in the index) for your join. Instead, create the two separate indexes with alter table tableA add index(key1) alter table tableA add index(acct) This will let MySQL determine which index(es) to use for optimizing the queries. If many other queries use both key1 and acct together, you may consider hcreating all three indexes, or the composite index as well as the separate index for acct. HTH, Tore. ----- Original Message ----- From: "Vivian Wang" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 25, 2003 11:52 AM Subject: mysql:Question about definition and index for performance > mysql, > > tableA, definition: key1 char(17) not null, > acct char(12), > other char (5) > > tableB is same definiton like tableA. > I add index for both tableA and tableB like: alter table tableA add > index(key1, acct) > > I will do left join with tableA and tableB like : > tableA left join on tableB on tableA.acct=tableB.acct where.... > tableB left join on tableA on tableB.acct=tableA.acct where.... > > The tableA has 32,000,000 records and tableB has 500,00 records. > > > I feel some thing wrong about how define the table fields, add index or > something, because I only use acct field for left join on. > I hope I can change some table definition or about add index to improve the > performance. > > > Also, I like to know what is difference between: > > alter table tableA add index(key1, acct) > > or > alter table tableA add index(key1) > alter table tableA add index(acct) > > Any suggestion? > Thanks. > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
