mysql performance question

Is a search through numbers (INT) faster than characters in an mySQL database? Regards开发者_StackOverflow社区, Thijs

---

In practice the difference will be small - it will increase depending on the relative length of the 2 fields. The question pre-supposes that 2 are interchangeable - which is a very bad assumption given that MySQL natively supports an ENUM data-type.

If it's that important to you, why not measure it?

---

Yes, it should be. An int is only 4 bytes, while in a text (varchar) might be considerably bigger.

Besides, if you have an index on the field you are searching, it will be smaller in size. Hence, you might need fewer disk accesses to do an index scan.

---

If the INT and VARCHAR consumes the same amount of space the difference should be negligible, even though INT probably will come out on top.

Databases don't use black magic. They need to physically access data like everyone else. Table rows consume disk space. Reading 100 mb is faster than reading 200 mb. Always.

Therefore. this affects everything. Smaller rows means more rows per "block". More rows per block means more rows fetched per disk access. Fewer blocks in total means that a larger percentage of the rows will fit in various buffer caches.