beer planet

• Damien Seguy of Nexen Services presents
• easiest session of all (phew, that's a relief)
• clever SQL recipes
• tweaking SQL queries
• shows an example where SELECT is ORDERED by a column that is actually an enum.
• an enum is both a string and a number
• sorted by number
• displayed as string
• can be sorted by string if it's cast as string
• compact column
• compacts storage
• faster to search
• if (var)char is turned into enum, some space can be saved, shows example
• random order
• order by rand(1) – obviously
• the integer parameter is actually a seed
• really slow, also obviously, especially for larger tables because it has to order first, then apply rand() to the list
• another solution is to add an extra column, put random values into it, and add index, then
• UPDATE tbl SET chaos=RAND();
• random extraction
• SELECT id, col FROM tbl JOIN
    (SELECT CEIL(RAND() *
      (SELECT MAX(id) FROM tbl)) AS r)
  AS r2 ON id=r;
• for multiple rows, Damien offers a solution involving an integer table (see his own slides). This solution is supposed to be really fast.
• integer table
• generate a table with only 10 integers
• do cross joins to get 100, 10,000, etc integers
• 10mil rows takes only 13 seconds (on his laptop), then either store it in another table or just use the result set
• yet again, code is shows, so see slides
• generating alphabet
• shows a handy query
• generating text
• offers a long query that uses ELT(), CONCAT(), and RAND() (see slides)
• a SELECT that builds words uses GROUP_CONCAT()
• looks pretty useful
• ASCII art
• SELECT version, REPEAT('*', percentage * 2) AS bars FROM php_versions;
• shows a nifty result set with stars as fillers in percentage bars
• shows another result set, similar to the one above, with spaces as fillers and stars at the end
• shows another example that looks like some sort of a spaceship, from mysql forge
• GROUP_CONCAT (can I get a woot? I love this function)
• CONCAT() and CONCAT_WS() now for groups
• ORDER BY
• SEPARATOR
• limited to 1kb by default, change group_concat_max_len
• shows example (see slides)
• how to get a second to last value?
• uses GROUP_CONCAT, looks horribly long and inefficient, there has to be a better way
• somebody suggests using ORDER BY with LIMIT
• rankings
• uses MySQL vars
• not as many people as I thought seem to know about it, judging by the low amount of raised hands
• SET @var:=3
• SELECT @var;
• variables are not shared between connections, destroyed at the end of the connection
• uses LEAST()
• see slides
• agile loading
• SET @i:=0
• LOAD DATA INFILE '/tmp/stats.txt'
  INTO TABLE statPHPload
    (@date, @php, @country, @continent)
  SET
    id = 0,
    period = date(STR_TO_DATE(@date, '%d-%b-%y')),
    rank = (@i := @i + 1),
    php = CAST(REPLACE(@php, ',', '.') AS DECIMAL),
    country = @country;

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.