• REPLACE INTO: think twice

    December 17, 2008

    The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation. Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO's. […]

  • Using memcached functions for MySQL; an automated alternative to Query Cache

    December 15, 2008

    There's a lot of buzz around memcached. memcached is widely used, and has clients for many programming languages and platforms. TangentOrg have developed a memcached client in the form of MySQL UDFs (User Defined Functions). I wish to discuss the memcached functions for MySQL: if and how they should be used. Disclaimer: I do not […]

  • Installing memcached functions for MySQL

    December 10, 2008

    tangent.org provide a memcached client for MySQL, in the form of UDFs. The provided set of functions allow for connecting to a memcached server, putting values in the cache, getting values, invalidating, utilizing increments etc.

    The code is not (yet?) available in binary format, so the libraries need to be compiled and installed manually. Following is a quick installation HOWTO for Linux users.

  • MySQL's character sets and collations demystified

    December 8, 2008

    MySQL's character sets and collations are often considered as a mystery, and many users either completely disregard them and keep with the defaults, or set everything to UTF8.

    This post will attempt to shed some light on the mystery, and provide with some best practices for use with text columns with regard to character sets.

  • Dynamic sequencing with a single query

    December 3, 2008

    It is a known trick to use a session variable for dynamically counting/sequencing rows. The way to go is to SET a variable to zero, then use arithmetic within assignment to increment its value for each row in the SELECTed rows.

    But can it be achieved with one query only? That's more of a problem... I'll provide such a solution, albeit not a pretty one.

  • Selecting a specific non aggregated column data in GROUP BY

    December 1, 2008

    In a GROUP BY query, MySQL may allow specifying non aggregated columns. For example, using MySQL's world database, I can get the number of countries per continent, along with a "sample" country.
    What if I want to choose that "sample" country? For example, for each continent, I wish to show the country with the largest population. To simply see the largest population, I would use MAX(Population). But which country is referred? I wish to provide a solution which does not involve sub-queries, HAVING or JOINs.

 
Powered by Wordpress and MySQL. Theme by openark.org