Effectiveness Implications of ORDER BY COALESCE in MySQL
My general problem is that I want users to be able to essentially add an arbitrary number of fields of different types to bind to items. Therefore, one solution I am considering is this:
table `items`
item_id | name
table `parameters`
parameter_id | name | type
table `values`
item_id | parameter_id | datetimevalue | datevalue | integervalue | etc...
If the user wants to add a Date of Birth parameter to some of their items, we will add one parameter to the parameter table and then one entry in the values table for each item he wants to have that parameter, with the date going in the datevalue column and all the rest of the fields are null.
To order his items by "Date of birth", if 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 work? Would it be useful to use indexes? Will this do unnecessary work?
My general question is, is this approach good practice? Is there a better way to do this?
This ORDER BY COALESCE ... will not be able to use the index. Is COALESCE important? It looks like if you look at one parameter, column ordering will suffice because all values will be of the same type.
This query will be able to use the index (parameter_id, datetimevalue, datevalue, integervalue) if you just did "ORDER BY datetimevalue, datevalue, integervalue".
Disadvantages: 1) it looks a little messy 2) if you have a lot of value columns and if the value table is large this index will be wasted and read / write.
You might be better off just adding a "sort_order" column (or whatever) to your value table and specifying that. Also, if you really want COALESCE because you want to sort values of different types, you can choose the sort_order calculation that will do the right thing.
source to share