Performance implications of ORDER BY COALESCE in MySQL

Question

My general problem is that I want users to be able to, in effect, add an arbitrary number of fields of different types to associate with items. So one solution I am considering is the following:

table `items`
item_id  |  name  

table `parameters`  
parameter_id  |  name  |  type

table `values`  
item_id  |  parameter_id  |  datetimevalue  |  datevalue  |  integervalue  |  etc...

If a user wants to add a "Date of birth" parameter to some of his items, we would add one parameter to the parameters table, and then one entry in the values table for each item that he wants to have this parameter, with the date going in the datevalue column and all other 'value' fields left null.

To order his items by "Date of birth", supposing this parameter has parameter_id=1, I would do

SELECT * from 
items
join values on items.item_id = values.item_id
join parameters on parameters.parameter_id = values.parameter_id

where parameter_id = 1
order by coalesce(values.datetimevalue, values.datevalue, values.integervalue...)

My specific question is, will this ORDER BY be performant? Will it make good use of indices? Will it do unnecessary work?

My general question is, is this approach good practice? Is there a better way to do this?

Answer 1

You are talking about EAV modelling.

Have a look at EAV modelling

Answer 2

This ORDER BY COALESCE... won't be able to make use of an index. Is the COALESCE important? It seems like if you are looking at a single parameter, ordering by the columns would be enough because all of the values will be of the same type.

This query would be able to make use of an index on (parameter_id, datetimevalue, datevalue, integervalue) if you just did "ORDER BY datetimevalue, datevalue, integervalue".

The downsides: 1) it looks a little messy 2) if you have lots of value columns and if your values table is going to be large, that index going to waste space and reads/writes.

You might be better off if you just add a "sort_order" (or something) column to your values table and index that instead. Also, if you really do need the COALESCE because you want to sort values of different types, you can choose a sort_order calculation that will do the right thing.