If both (user_id,delivery_id) can be made primary key, then the second index would not be required. But the performance of the query would be better if it reads one index rather than indexes on each COLUMN in the "WHERE CLAUSE"
On 5/15/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > Since user_id is a primary key. It should work either with any of the > column > and with both the column. > > Any suggestion. > > Thanks > > On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > > On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati > > <[EMAIL PROTECTED]> wrote: > > > Hi all, > > > > > > Below is the user_delivery table structure. > > > > > > CREATE TABLE `user_delivery` ( > > > `user_id` decimal(22,0) NOT NULL default '0', > > > `delivery_id` decimal(22,0) NOT NULL default '0', > > > `send_to_regulator` char(1) default NULL, > > > PRIMARY KEY (`user_id`), > > > KEY `user_delivery_comp1` (`user_id`,`delivery_id`), > > > CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES > > > `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > > > > > According to me user_delivery_comp1 index can be dropped and new index > > can > > > be created on delivery_id column. I would to know that the changes will > > work > > > or not. Yours suggestion regarding this table structure. > > > > > > Thanks, > > > -- > > > Krishna Chandra Prajapati > > > > > > > Define work. > > > > The effect should be something like: > > Queries that have where clauses for delivery_id but not user_id would > > be able to use an index. > > Queries that have where clauses for delivery_id and user_id might not > > be able to use as much of an index. Depending on your version of mysql > > merge index may apply, but I am not knowledge enough to comment of the > > performance differences between the two. I would assume a composite > > index when available would generally be more ideal. > > > > > > -- > > Rob Wultsch > > [EMAIL PROTECTED] > > wultsch (aim) > > > > > > -- > Krishna Chandra Prajapati > MySQL DBA, > Ed Ventures e-Learning Pvt.Ltd. > 1-8-303/48/15, Sindhi Colony > P.G.Road, Secunderabad. > Pin Code: 500003 > Office Number: 040-66489771 > Mob: 9912924044 > URL: ed-ventures-online.com > Email-id: [EMAIL PROTECTED] >
