DON'T! Just don't! (wild queries gone bad)

If you have a Roku SoundBridge, you'll probably find Radio Roku interesting. It's a great product, but however, the "radio roku" is an extremely slow website. Today I have searched for stations playing "electro" music and here's what I got:

Error SELECT stations.id AS id, stations.name AS name, stations.rating AS rating, if(stations_rank.id is null, 1000000000, stations_rank.id) AS rank FROM stations LEFT JOIN countries ON stations.country = countries.id and countries.languages_id= LEFT JOIN languages ON stations.language = languages.id and languages.languages_id= LEFT JOIN genres ON genre = genres.id and genres.languages_id= LEFT JOIN stations_rank ON stations_rank.stream_id = stations.id WHERE (name like '%electro%' or description like '%electro%' or location like '%electro%' or genres.value like '%electro%' or countries.value like '%electro%' or languages.value like '%electro%') ORDER BY rank ASC

Ok... so that's why the website is moving soooo slow. Here's a practical list of "don't do this" from a single query:

• model logic in data retrieval: if(stations_rank.id is null, 1000000000, stations_rank.id) AS rank. You should avoid this. It's much better to calculate this as an additional field and not to do an evaluation every single time you retrieve results (especially as this calculated field is probably required in looots of listings, what if you want to change this logic?). Also try to avoid SQL constructs specific to a database engine, in this case IF(cond, a, b). Well, if you'll live all your life in MySQL you may use some functions, ok...


• order by calculated field: ORDER BY rank. Makes any query cache useless and is not properly indexed by most engines.


• field like '%value%'. Never use this on a table larger than 1000 rows that you care about. If on MySQL, try the fulltext indexes available on MyISAM. If not, use a solution for a similar fulltext search.


• condition_on_field1 OR condition_on_field2. Most likely it won't be indexed.

Ok, so why would we care? Because of scalability. We want those tens of millions of users hit our application and browse those pages and do this and that. And we don't want a datacenter just to handle their simple queries (which is the other solution for badly written queries).

And of course, we all want to sell. Roku has a couple of very good under-promoted audio players (SoundBridge and SoundBridge Radio are brilliant, probably except for a minor glitch with AAC streams). If I would have seen radioroku.com before the product, I'd think "neeah, that's too slow"...