Re: Table Structure

["Rob Wultsch" <wultsch@xxxxxxxxx>]

On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
<prajapatikc@xxxxxxxxx> 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
wultsch@xxxxxxxxx
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx