I first read High Performance MySQL, 2nd edition about a year ago, when it first came out. I since re-read a few pages on occasion. In my previous posts I've suggested ways to improve upon the common ranking solution. Very innovative stuff! Or... so I thought. I happened to browse through the book today, and […]
This post follows SQL: Ranking without self join and On user variables evaluation order. I wish to share some insights with regard to user variables evaluation, as well as provide yet another ranking solution, which attempts to overcome the uncertainty factor with user variables. There will be hand waving in this post (albeit empirical hand […]
There is something very unclear about what is defined and is undefined with regard to the order by which user variables are evaluated, even within the MySQL documentation itself. I wish to present some examples and draw a conclusion. Since I will following state there's missing information, I would greatly appreciate any educated comments. The […]
The common way of solving the classic SQL problem of ranking, involves a self join. I wish to present a different solution, which only iterates the table once, and provides the same output. The ranking problem Given a table with names and scores (e.g. students exams scores), add rank for each row, such that the […]
I find myself converting more and more customers' databases to InnoDB plugin. In one case, it was a last resort: disk space was running out, and plugin's compression released 75% space; in another, a slow disk made for IO bottlenecks, and plugin's improvements & compression alleviated the problem; in yet another, I used the above […]
There is a serious bug with the sphinx storage engine, introduced in 0.9.9-RC2 (and which has not been fixed in latest revisions, as yet - last checked with rev 2006). I would usually just revert to an older version (0.9.9-RC1 does not contain this bug), but for the reason that RC2 introduces an important feature: […]
In some of my previous posts I've used a numbers table, like one holding values 1, 2, 3, ..., 255. Such table can be used for string walking, joining with other tables, performing iterations. The existence of number tables has always been a little pain. Yes, they're very, very simple, but they need to be […]