3

I checked this SO post:

What's the difference between primary key, unique key, and index in MySQL?

and found the statement:

Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.

Based on this, I have two questions:

  • Am I safe in assuming that there is no performance benefit to creating an index on a primary key itself because the primary key, by design, is an index?

Perhaps the more important question:

  • If you are doing the classic example people cite, doing SELECT based on lastName and firstName, and that table has a primary key that you SELECT by frequently as well, would you create the index as (primary_key, lastName, firstName) or just (lastName, firstName) since the primary key is already an index?
Community
  • 1
  • 1
Carvell Fenton
  • 2,341
  • 6
  • 23
  • 30
  • Why do you want to create an index on something that has index by default? – Andrey Sep 20 '11 at 13:05
  • I am trying to understand the behaviour of the primary key. From my reading, I had the impression that even if the table had a primary key, if I did a SELECT based on lastName without an index, MySQL would scan the whole table. If I made an index based on some combination of lastName and firstName it would solve this issue. However, if that is true, then what does the primary key get me except uniqueness? Trying to ascertain how to get best SELECT performance. – Carvell Fenton Sep 20 '11 at 13:09
  • it gives you fast SELECT by PK. By the way I am surprised that if you have FirstName+LastName and query by Last Name it is fast. – Andrey Sep 20 '11 at 13:14
  • I am not saying that query is fast. Without creating an index on lastName, it isn't fast, just as you say. I am just trying to understand how to structure the indexes correctly. Hope that clarifies. – Carvell Fenton Sep 20 '11 at 13:18
  • 1
    in case of simple queries like `SELECT A FROM B WHERE C` you have two options: whole table scan or index lookup, if you want to have second then you WHERE should use columns only from existing index. – Andrey Sep 20 '11 at 13:23

2 Answers2

1

To your first question, yets, you're safe to assume that.

To the second question:

Indexes help to speed up searching - it's like an index in a book. They can help the DB engine jump to the correct record, just as an index can help you jump to the right page in a book.

The benefit to indexes that you might create youself depends on how you intend to search the data.

In your example, I'd create an INDEX on the name fields if you're going to search on them in your app.

David
  • 72,686
  • 18
  • 132
  • 173
  • Finally done editing my own answer. Sorry I mis-read the question at first. – David Sep 20 '11 at 13:10
  • 1
    So by that you mean create the name fields index without including the primary key as part of that index definition? And then, if I am SELECTING by primary key only, no point to index that since it is already an index? – Carvell Fenton Sep 20 '11 at 13:15
  • Absolutely - that's exactly what I mean. – David Sep 20 '11 at 14:15
  • Thanks, I think yours and the other comments have cleared it up for me. Much appreciated! – Carvell Fenton Sep 20 '11 at 14:41
0

If there's an index on PRIMARY_KEY, and an index on LastName, FirstName, that does not automatically mean an index on PRIMARY_KEY, LastName, FirstName.

However, in cases like this the primary key field is often redundant as you have a different index to work with.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055