PHP and MySQL data storage solution

How best to store data?

[barns101]

I'm going to be storing membership data and searching, retrieving and displaying it with PHP. One of the fields will be ethnicity. In terms of speed and general performance, which of the following solutions would be best?

Solution 1
Store the data as enum('Caucasian', 'African', 'Mixed Race', 'Asian') etc... and then simply echo the data from MySQL straight onto the page.

I guess that will use more database space when there are lots of records, and queries could be slower. So...

Solution 2
Store the data as enum(1, 2, 3, 4) etc... and then have a switch statement on the person's profile page like this:

switch($row[ethnicity])
{
case 1:
echo 'Caucasian';
break;
case 2:
echo 'African';
break:
etc...
}

I guess the second solution would make the query quicker but slow down the page because PHP would have to a little more work.

I'm going to have a few fields similar to this one and wondered which method would be the most effective overall? I hope there's someone with experience of this type of scenario who knows what's best. ;)

Thanks in advance.

[StupidScript]

PHP is pretty quick, especially when compared to MySQL queries.

MySQL queries are more processor/storage intensive, too.

Solution 1 would result in increased storage and retrieval requirements.

Solution 2 is good: Quick queries and let PHP parse the result into useable form without significant RAM resources.

A third option that would increase PHP's parsing speed but also increase the RAM overhead a bit would be to create arrays out of the possible values and then use the enum's to pick the correct element.

$ethnic=array("Caucasian",..);

...

$this_ethnic=$ethnic[$row["ethnicity"]];

However Solution 2 would be faster, all around.

Solution 2 may not be as convenient for you as Solution 3, because adding a new ethnicity requires a new case statement for each one, instead of just adding on to the array, but 2 would have better performance than 3 because of the array's volatile memory storage requirements during PHP processing.

[edited by: StupidScript at 8:17 pm (utc) on Aug. 11, 2006]

[barns101]

Option 2 it is then, thanks. :)

[pinterface]

Solution 1 will most certainly not result in increased storage requirements. MySQL converts enum values into indexes, essentially doing the switch thing of solution 2 (or the array index thing of StupidScript's idea) for you; it does not store a string with every record. (see the storage requirements [mysql.com] of an enum field.)

Handling small, fixed sets as in solution 1 is what enum is meant for. Why go around your database, when you can work with it?

[barns101]

Thanks pinterface, I wasn't aware of that. :)

I did some tests with enums (strings or numbers) and both were pretty much the same speed.

Looks like I'll go with option 1 then, as that would be easier.

[StupidScript]

I feel the need to update ... :)

pinterface is correct. The reference to storage requirements got my brain all mushed up, and so I went seeking some explanation from the mailing.database.mysql group. Got a great explanation about WHY enum-type fields only take up 1-2 bytes of DATA storage (not so for table definition storage, but still insignificant), and some good suggestions for opting for other field types than enum.

I don't think it's against the ToS if I link to the thread [groups.google.com] for the general good ... is it?

[edited by: StupidScript at 5:59 pm (utc) on Aug. 15, 2006]

[barns101]

Thanks, StupidScript. I always enjoy furthering my understanding of PHP/MySQL.