0

MySQL Conference Liveblogging: MySQL Hidden Treasures (Thursday 11:55PM)


Posted by Artem Russakovskii on April 17th, 2008 in Databases

  • 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 and blogger. 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.

Tags: , , ,