I've been inserting some numbers as INT UNSIGNED in MySQL database. I perform search on this column using "SELECT. tablename WHERE A LIKE 'B'. I'm coming across some number formats that are either too long for unsigned integer or have dashes in them like 123-456-789. What are some good options for modifying the table here? I see two options (are there others?): Make another column (VARCHAR(50)) to store numbers with dashes. When a search query detects numbers with dashes, look in this new column. Recreate the table using a VARCHAR(50) instead of unsigned integer for this column in question. I'm not sure which way is the better in terms of (a) database structure and (b) search speed. I'd love some inputs on this. Thank you. Update: I guess I should have included more info. These are order numbers. The numbers without dashes are for one store (A), and the one with dashes are for Amazon (B; 13 or 14 digits I think with two dashes). A's order numbers should be sortable. I'm not sure if B has to be since the numbers don't mean anything to me really (just a unique number). If I remove the dashes and put them all together as big int, will there be any decrease in performance in the search queries?

Reputation: 4080

MySQL: optimal column type for searching

I've been inserting some numbers as INT UNSIGNED in MySQL database. I perform search on this column using "SELECT. tablename WHERE A LIKE 'B'. I'm coming across some number formats that are either too long for unsigned integer or have dashes in them like 123-456-789.

What are some good options for modifying the table here? I see two options (are there others?):

Make another column (VARCHAR(50)) to store numbers with dashes. When a search query detects numbers with dashes, look in this new column.
Recreate the table using a VARCHAR(50) instead of unsigned integer for this column in question.

I'm not sure which way is the better in terms of (a) database structure and (b) search speed. I'd love some inputs on this. Thank you.

Update: I guess I should have included more info. These are order numbers. The numbers without dashes are for one store (A), and the one with dashes are for Amazon (B; 13 or 14 digits I think with two dashes). A's order numbers should be sortable. I'm not sure if B has to be since the numbers don't mean anything to me really (just a unique number).

If I remove the dashes and put them all together as big int, will there be any decrease in performance in the search queries?

Upvotes: 0

Answers (3)

mkk

Reputation: 7693

the most important question is how you would like to use the data. What do you need? If you make a varchar, and then you would like to sort it as a number, you will not be able to, since it will be treating it as string.. you can always consider big int, however the question is: do you need dashes? or can you just ignore them on application level? if you need them, it means you need varchar. in that case it might make sense to have two columns if you want to be able to for example sort them as numbers, or perform any calculations. otherwise probably one makes more sense. you should really provide more context about the problem

Upvotes: 2

Will

Reputation: 900

Mysql has the PROCEDURE ANALYSE , which helps you to identify with your existing data sets. here's some example.

Given you are running query WHERE A LIKE 'B' mainly. You can also try full text search if "A" varies a lot.

Upvotes: 1

Derek

Reputation: 23268

I think option 2 makes the most sense. Just add a new column as varchar(50), put everything in the int column into that varchar, and drop the int. Having 2 separate columns to maintain just isn't a good idea.

Upvotes: 0

MySQL: optimal column type for searching

Answers (3)

Related Questions