CODEWITHSUNDEEP
mysqldatabase
John
John

Reputation: 7067

Do I have to use a integer auto-inc primary key with MySQL?

I have a MySQL database table which has over 35 million rows. I originally created an id field as the primary key. But this id field was just an auto_increment value which was not part of my data. But my main queries would be on a field called zipcode.

Queries made with WHERE zipcode='' seem to be very slow. I realised that indexing the zipcode field speeds this up. However, I have then noticed that each field that is indexed takes up certain amount of space. The indexing the zipcode fieled only takes up 75MB but the "id" field primary key index takes up over 3GB.

As I do not use the "id" field for anything, I have now removed the id field and just keeping the index on the zipcode field. I just felt that the id field was taking up space for no reason. Is there any disadvantages of not having a unique id field?

Any other ways I can speed up queries to this database table?

Upvotes: 0

Views: 65

Answers (1)

O. Jones
O. Jones

Reputation: 108641

There are some significant advantages to be had by maintaining a primary key, even a so-called surrogate primary key like an autoincremented id column.

For one thing, it lets you edit your table if you need to using tools like phpMyadmin or MySQL workbench. If there's no primary key, those tools can't update your table.

For another thing, in InnoDB the primary key is a tacit part of every index, so operations using indexes get faster. For example, if you want to find the entries in your table with the top populations, you could do this:

SELECT *
  FROM tabl t
  JOIN ( SELECT ID
           FROM tabl
          ORDER BY population DESC
          LIMIT 10
       )  s ON s.ID = t.ID

If you index population and you have a primary key, this will be very fast, for example.

You say your ID index consumes 3GB. Rounding up, a 1TB disk drive costs US$100. So, your 3GB of index are using US$0.30 worth of resources. Saving the space is a false optimization.

Don't delete the primary key.

Upvotes: 1

Related Questions