it goes without saying 1. primary key and unique key would do unique scan which is fastest of all scan 2 Index would do range scan, which would be comparitivly slower.
regards anandkl On 5/16/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > 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] >
