MySQL Forums
Forum List  »  Optimizer & Parser

how to create the index for three fields?
Posted by: justin ux
Date: April 24, 2007 08:35PM

Hi,
I want to create index for two tables when excute the following sql statement.
which way is better and will be faster for query? Indexes in both table S23 and tab1 will be created.

1. create a single index of three fields
alter table tab1 add index index3 (msg_id ,recipientaddr,msg_internal_seq);

or
2 create 3 indexes for each fileds
altern table tab1 addindex id(msg_id) , add index r_addr (recipientaddr) , add index msgseq(msg_internal_seq) ;


SQL:
create table dv_temp SELECT tab1.s1 s1, S23.s2 s2, S23.s3 s3, tab1.s4 s4,
tab1.origaddr origaddr, tab1.recipientaddr recipientaddr FROM tab1 left join S23 on tab1.msg_id = S23.msg_id and tab1.recipientaddr = S23.recipientaddr and tab1.msg_internal_seq=S23.msg_internal_seq;



Thanks!
Justin

Options: ReplyQuote


Subject
Views
Written By
Posted
how to create the index for three fields?
3476
April 24, 2007 08:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.