Questions on Primary keys and indexes

pearl2 · #1 (**permalink**) 02-02-09, 11:52

Hi all,

The following are questions that I've always wanted to find answers to:

1) If a column is defined as a Primary Key, is it automatically indexed? Specifically, do we need the second line "INDEX(`id`)" in the example below?

Code:

`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
INDEX(`id`),

2) Do we get the same results with the following two ways of declaring indexes?

Code:

/* Indexes are jointly declared */
`ida` MEDIUMINT(8) UNSIGNED NOT NULL,
`idb` MEDIUMINT(8) UNSIGNED NOT NULL,
INDEX(`ida`, `idb`),

/* Indexes are singly declared */
`ida` MEDIUMINT(8) UNSIGNED NOT NULL,
`idb` MEDIUMINT(8) UNSIGNED NOT NULL,
INDEX(`ida`),
INDEX(`idb`),

I hope the questions aren't "stupid"

Many thanks in advance

r937 · #2 (**permalink**) 02-02-09, 11:56

1) yes, a PK is automatically indexed

2) no -- only one index will be used per query, so the separate indexes are not as useful as the composite index when filtering needs to be done on both columns

pearl2 · #3 (**permalink**) 02-02-09, 12:00

Quote:

Originally Posted by r937

1) yes, a PK is automatically indexed

2) no -- only one index will be used per query, so the separate indexes are not as useful as the composite index when filtering needs to be done on both columns

Many thanks, r937

With regard to #2, does that mean it's preferable to use a composite index?

r937 · #4 (**permalink**) 02-02-09, 14:03

Quote:

Originally Posted by pearl2

does that mean it's preferable to use a composite index?

that depends on what results your query expects to return and what search criteria are being used

mike_bike_kite · #5 (**permalink**) 02-02-09, 14:49

Quote:

With regard to #2, does that mean it's preferable to use a composite index?

If you're using both ida and idb in your where clause then it's best to have the composite index. If you're sometimes going to be using ida in the where clause and at other times only idb then it's better to have the two separate indexes. If you always have ida in your where clause and sometimes idb as well then either index will work. You could easily have the best of both worlds and have a composite index on ida, idb and another separate index just on idb.

pearl2 · #6 (**permalink**) 02-02-09, 21:12

Great thanks, mike_bike_kite!

I didn't know such differences exist - glad I asked

When you say both ida and idb in the same WHERE clause, do you mean a query such as:

Code:

SELECT * FROM some_table WHERE ida=1 AND idb=5;

r937 · #7 (**permalink**) 02-02-09, 22:28

yes, the composite index would be used for that query

it's like if i ask you to look up a name in the phone book (which is in sequence by last name, first name) -- if i ask you to find all Smiths (which is a lot) whose first name starts with T, you'd use the secondary index to scan ahead through all the Smiths easily

but if i asked you to find every Smith who lives on Elm street, you'd have to read through all the Smiths (can only use the front part of the index)

and if i asked you to find everyone who lives on Elm street, you'd have to read through the whole phone book (can't use the index at all)

so proper indexing is determined by what queries you're going to be running

mike_bike_kite · #8 (**permalink**) 02-03-09, 05:07

Quote:

Originally Posted by pearl2

When you say both ida and idb in the same WHERE clause, do you mean a query such as:

Code:

SELECT * FROM some_table WHERE ida=1 AND idb=5;

Yes that's exactly what I mean. Rudy's explanation is pretty good so I'd read that a few times. Oddly if you change the AND in your query to an OR then we'd be back to using 2 indexes again - can you see why?

Indexing and performance tuning can be quite an in depth subject (but it becomes second nature with enough experience) so I'd concentrate more on just ensuring each table has a primary key. When you do find a query that runs slowly then I'd start to investigate different types of index etc at that point.

pearl2 · #9 (**permalink**) 02-03-09, 06:47

Thanks for the enlightenment, r937 and mike_bike_kite!

At least now I know declaring indexes jointly (composite) is not the same as declaring them singly - that itself is an important new knowledge for me.

Does the same concept apply to primary keys? Specifically, the definition "PRIMARY KEY(`ida`, `idb`)" is a composite Primary Key and is different from the two definitions "PRIMARY KEY(`ida`)" and "PRIMARY KEY(`idb`)". Am I on the right track?

guelphdad · #10 (**permalink**) 02-03-09, 08:59

You can only have one primary key in a table so you can't declare two like in your example. It can be a multi-column key though.
This is valid:
PRIMARY KEY(`ida`, `idb`)

This isn't:
"PRIMARY KEY(`ida`)" and "PRIMARY KEY(`idb`)

pearl2 · #11 (**permalink**) 02-03-09, 23:13

Great thanks guelphdad!

Ah, that was a bad one by me

So does a composite primary key work the same way as a primary key with a single value?

r937 · #12 (**permalink**) 02-03-09, 23:38

Quote:

Originally Posted by pearl2

So does a composite primary key work the same way as a primary key with a single value?

you mean, a PK with a single column

yes, it works the same way, but with two columns instead of one

in essence, you could have repeating values in either of the columns of a composite PK, but the combination of values must be unique

pearl2 · #13 (**permalink**) 02-04-09, 09:26

Thanks, r937!

Just one more doubt:

Code:

PRIMARY KEY('ida', 'idb');
INDEX ('ida');

Is the INDEX redundant, since ida is defined as part of a composite PRIMARY KEY?

Lots of

to all of you out there. Thank you so much

mike_bike_kite · #14 (**permalink**) 02-04-09, 09:41

it's redundant

r937 · #15 (**permalink**) 02-04-09, 12:32

Quote:

Originally Posted by pearl2

Code:

PRIMARY KEY('ida', 'idb');
INDEX ('ida');

please note, both of those will fail

you cannot declare a PK or an index on strings!!

do this instead --

Code:

PRIMARY KEY ( ida, idb )

see the difference?

Internet Services

Web Development

Digital Marketing

Consumer Tech