0
MySQL Conference Liveblogging: MySQL Hidden Treasures (Thursday 11:55PM)
Posted by Artem Russakovskii on April 17th, 2008 in Databases
| Share |
- 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.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
