'Natural sorting' with MySQL?

Question

I'm trying to query a Wordpress database and get the post titles to sort in a correct order. The titles are formatted like this: Title 1, Title 2.. I need to sort them in ascending order, how can I do this? If I just sort them ascending they will come out like: 1,10,11...

Right now my order by statement is this but it does nothing:

ORDER BY CONVERT(p.post_title,SIGNED) ASC;

Answer 1

If the # is always at the end like that you can do some string manipulation to make it work:

SELECT *, CAST(RIGHT(p.post_title,2) AS UNSIGNED) AS TITLE_INDEX 
FROM wp_posts p
ORDER BY TITLE_INDEX asc

Might have to tweak it a bit assuming you may have 100+ or a 1000+ numbers as well.

Answer 2

Per-row functions are a bad idea in any database that you want to scale well. That's because they have to perform the calculation on every row you retrieve every time you do a select.

The intelligent DBA's way of doing this is to create a whole new column containing the computed sort key, and use an insert/update trigger to ensure it's set correctly. The means the calculation is performed only when needed and amortises its cost across all selects.

This is one of the few cases where it's okay to revert from third normal form since the use of the triggers prevents data inconsistency. Hardly anyone complains about the disk space taken up by their databases, the vast majority of questions concern speed.

And, by using this method and indexing the new column, your queries will absolutely scream along.

So basically, you create another column called natural_title mapped as follows:

title          natural_title
-----          -------------
title 1        title 00001
title 2        title 00002
title 10       title 00010
title 1024     title 01024

ensuring that the mapping function used in the trigger allows for the maximum value allowed. Then you use a query like:

select title from articles
order by natural_title asc