CODEWITHSUNDEEP
mysqlsql
avon_verma
avon_verma

Reputation: 1249

Does mysql prefer number types or letter types?

For a mysql database, is it better to use (for performance etc...) letters or numbers to store data?

Here's what I mean, 3 examples: (125015 represents the Arts & Entertainment category)

  1. Column varchar(255) named category with a row value Arts and Entertainment.
  2. Column varchar(255) named category_num with a row value 125015
  3. Column bigint(255) named category_int with a row value 125015

So, for a select statement.. Querying from which column would let the server perform best?

 1. SELECT from table WHERE
    category = 'Arts and Entertainment'
 2. SELECT from table WHERE
    category_num = '125015'
 3. SELECT from
    table WHERE category_int = 125015

Also, would there be any benefits (compared to exaple 1) for using example 2, which stores numbers inside a varchar, rather than int?

Upvotes: 0

Views: 100

Answers (1)

user151323
user151323

Reputation:

The smaller a data type is, the smaller will be an index over it and so faster will be the search.

Numbers clearly rate better than strings. String comparison is known to be very slow.

Example 2 will still be less effective than #3, though possibly somewhat faster than #1 since the string value in #2 is shorter, but anyway #1 and #2 are of the same performance category.

Upvotes: 1

Related Questions