SEO

#1 (**permalink**) 02-28-2008, 08:50 AM

Suppose one has the standard many-to-many setup

CREATE TABLE `a` (
`a_id` varchar(6) NOT NULL default '',
`a_desc` varchar(40) NOT NULL default '',
PRIMARY KEY (`a_id`)
)

CREATE TABLE `b` (
`b_id` varchar(6) NOT NULL default '',
`b_desc` varchar(40) NOT NULL default '',
PRIMARY KEY (`b_id`)
)

Plus XREF table:

CREATE TABLE `x` (
`a_id` varchar(6) NOT NULL default '',
`b_id` varchar(6) NOT NULL default '',
PRIMARY KEY (`a_id`,`b_id`)
)

Now this provides an index for getting from a to b, but for efficiency an
index should be supplied for b to a.
So here's the question.
Which is the better (more efficient) index to define,
CREATE INDEX b2a ON `x` (`b_id`)
or
CREATE UNIQUE INDEX b2a ON `x` (`b_id`, `a_id`)

#2 (**permalink**) 02-28-2008, 08:50 AM

Paul Lautman wrote:
> Suppose one has the standard many-to-many setup
>
> CREATE TABLE `a` (
> `a_id` varchar(6) NOT NULL default '',
> `a_desc` varchar(40) NOT NULL default '',
> PRIMARY KEY (`a_id`)
> )
>
> CREATE TABLE `b` (
> `b_id` varchar(6) NOT NULL default '',
> `b_desc` varchar(40) NOT NULL default '',
> PRIMARY KEY (`b_id`)
> )
>
> Plus XREF table:
>
> CREATE TABLE `x` (
> `a_id` varchar(6) NOT NULL default '',
> `b_id` varchar(6) NOT NULL default '',
> PRIMARY KEY (`a_id`,`b_id`)
> )
>
> Now this provides an index for getting from a to b, but for efficiency an
> index should be supplied for b to a.
> So here's the question.
> Which is the better (more efficient) index to define,
> CREATE INDEX b2a ON `x` (`b_id`)
> or
> CREATE UNIQUE INDEX b2a ON `x` (`b_id`, `a_id`)
>
>

There is not "better index" without knowing how you're using them.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

The better index