Can declare all primary keys bigint make much difference in MySQL?

Question

I have some small tables that don't need the bigint primary key, they won't get that big, but, all tables have bigint primary key as standard.

Can this affect my performance or mysql is smart on that?

I wouldn't like to change the PKs to int on those tables, but if it can slow me down, surely I will.

Please don't write signatures in questions. 2 years, 2 months! — Lightness Races in Orbit, Sep 11 '11 at 02:19
saying Thanks is not much of a signature ¬¬, but oh well, ok xD — Jonathan, Sep 11 '11 at 02:31

score 2 · Answer 1 · answered Sep 11 '11 at 02:19

2

One of optimization rules for DBMS is "keep your data as small as possible" - so if you don't need bigint - declare it as an int (and change type when you need it)

answered Sep 11 '11 at 02:19

zerkms

249,484
69
436
539

score 1 · Answer 2 · answered Sep 11 '11 at 02:19

1

Based on benchmarks here using an BIGINT could increase the database size by a significant factor, which would affect performance, probably not noticeable until you reached a significant size.

answered Sep 11 '11 at 02:19

bcoughlan

25,987
18
90
141

score 0 · Answer 3 · answered Sep 11 '11 at 02:19

MySQL won't do this for you, as it (and you) never know(s) how big the tables will get. The performance benefits of changing BIGINT to INT on smaller tables is negligable, although it might be an idea to keep the BIGINT type in case your row count goes above INTs limits of 2147483647 (4294967295 unsigned). It is, however, advisable to keep your data in as compact a way as possible.

If it's a relatively small table, you might be better off going with MEDIUMINT actually. It's limits are 8388607 and 16777215 (unsigned).

Can declare all primary keys bigint make much difference in MySQL?

3 Answers3