Take the 2-minute tour ×

I'm gonna convert string to integer to optimize group by performance.

What do you guys think of the idea?

If applicable,then is there a built-in function to convert string to a unique integer in PHP?

share|improve this question
you can use crc32() function to create a unique integer of a string. or use md5 then convert from hexadecimal back to base-10. –  mauris Sep 20 '09 at 16:30
If the data is only integers, why are you storing it in string columns in the first place? Have you measured the performance cost or benefit? –  Jonathan Leffler Sep 20 '09 at 16:31
@Mauris: CRC-32 only gives you a reasonable probability of generating a unique integer for each string - it is by no means 100% deterministic. Quite small samples can run into collisions - different strings hashing to the same CRC-32 value. Using a bigger hash - MD5 (128 bits instead of 32 bits) or SHA-1 (160 bits) or SHA-256 (err, would you believe 256 bits) - is more likely to be unique but the conversion cost is considerably higher. –  Jonathan Leffler Sep 20 '09 at 16:34
@Mauris: I was using a CRC algorithm on time zone names from the Olson database, and got collisions in that set. Thinking back, I'm not sure now whether I was trying a 16-bit or a 32-bit CRC, but in a sample of a few hundred names, I was running into collisions. –  Jonathan Leffler Sep 20 '09 at 16:36

1 Answer 1

If all data in that column is int, then it should be int...

The main advantage you get is that a comparison between 2 ints only needs to compare 4 byte. A string that expresses the same number will (usually) be longer. So you should be able to get some performance out of it. But the main reason should be that a column that stoes int should be int.

The group by optimization would fall into the category "premature optimization"... And the main thing that will speed up a group by clause is not the datatype, but the index on the field

share|improve this answer

Your Answer


