Hi, I would like to know which is faster out of the below.
Primary Key Unique Key Indexing Give the numbering 1, 2 and 3 Thanks a lot On Thu, May 15, 2008 at 5:49 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > 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] >> > > -- 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]
