Sorting by unknown column type in mysql

Question

So I've got a users table; storing things like username, email, etc. I then have a user_settings table. Instead of standard columns like timestamp_logged_in, profile_views, etc., I have it organized as key/value combo. For example, the columns are:

user_settings_ID user_ID name value

A sample record would be: 82 2 'timestamp_logged_in' '2009-10-10 02:23:53'

This makes the settings a little more permeable. I don't need to have a column exist to store new user setting types. I'm concerned that doing this (which will store every value as a string, and I'll cast it to the appropriate type in the middle ware) will make it difficult to sort. So for example if I wanted to get a list of the users in order of last logged in, sorting would require an sql call such as:

SELECT user_ID FROM user.settings WHERE status='open' AND name='timestamp_logged_in' ORDER BY name

This would be sorting the found records as strings, however. Is there a way to make sure the sorting algorithm mysql is implemented based on a certain value type (eg. forcing it to treat the sort column as a string, integer, datetime, timestamp, etc.)?

Any thoughts would really be appreciated. Thanks. Oliver Nassar

Answer 1

You would have to manually cast it to the correct data type.

If you expect the table to get large, storing as key-value pairs like this, while it seems like a good idea at the start, invariably ends up with really crappy performance, as you can't create useful indexes. Think about it now instead of changing later.

Answer 2

Why not store the data in a "sortable" string representation .. "pre-casting to a sortable format"

instead of '11-28-2009 11:45' you could store it as '2009.11.28 11:45'

MySQL has the functions needed to typecast back to "datetime" or "date" in the "middleware".