• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Type integer or string: does it matter when querying?

#1
[eluser]codex[/eluser]
Let's say you have a 'color' field in your db, where 'color' represents the color of a (for instance) shoe. You could have brown, black, white. But you could also have the colors be represented by an integer: 1=brown, 2=black, 3=white.

Would it make a difference on performance when you query something like "where color = 'brown'" instead of "where color = 1" '?

#2
[eluser]Phil Sturgeon[/eluser]
Depends on how many levels of normalization you want to put yourself and your application through.

If I was doing this, I'd just store HEX codes: 000000=black, FFFFFF=white, etc.

Otherwise it's just a question of how often you will be adding, editing or deleting colors from the list. If not often, use the string.

#3
[eluser]codex[/eluser]
[quote author="pyromaniac" date="1229375763"]Depends on how many levels of normalization you want to put yourself and your application through.

If I was doing this, I'd just store HEX codes: 000000=black, FFFFFF=white, etc.

Otherwise it's just a question of how often you will be adding, editing or deleting colors from the list. If not often, use the string.[/quote]

Thanks. But it isn't really about color. It's about whether it would make a difference in performance to use a (tiny)int or string as the fieldtype. I prefer a string because it makes the db easier to read but I have always wondered if it would make a difference for the query.

#4
[eluser]xwero[/eluser]
A varchar type field is slower than a int field i believe but i'm not sure how you are going to use it in a way that it matters how the color is stored in the database?

pyromaniacs solution is the middle ground between speed and readability, because you can store it in a char field.

#5
[eluser]jaswinder_rana[/eluser]
char will be faster than varchar. So, i'll go with pyromaniac, unless ofcourse you want to create a table with colors and then use foreign key where you need it.

It really depends on your requirements and how you plan to use it. You have to decide or provide how you plan to use it.

Main difference here is size and ofcourse how often you plan to change data.

This might of interest to you. http://aggregator.foolab.org/node/26143

#6
[eluser]codex[/eluser]
Thanks guys, I think char will be my choice.

#7
[eluser]m4rw3r[/eluser]
You can also use hexdec() and dechex() to convert the color to int.

But use str pad with 0 when you use hexdec(), because it will remove all preceding zeroes:
Code:
$color = str_pad(dechex($number), 6, '0', STR_PAD_LEFT);

#8
[eluser]codex[/eluser]
[quote author="m4rw3r" date="1229466742"]You can also use hexdec() and dechex() to convert the color to int.

But use str pad with 0 when you use hexdec(), because it will remove all preceding zeroes:
Code:
$color = str_pad(dechex($number), 6, '0', STR_PAD_LEFT);
[/quote]

The color thing was just an example. What I meant was: store a key as int or as string?

#9
[eluser]xwero[/eluser]
m4rw3r i wonder if the speed of querying an int type field is a measure for nothing because of the dechex function?

#10
[eluser]m4rw3r[/eluser]
Probably


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.