'SQL' Tag

  • SQL trick: overcoming GROUP_CONCAT limitation in special cases

    July 21, 2010

    In Verifying GROUP_CONCAT limit without using variables, I have presented a test to verify if group_concat_max_len is sufficient for known limitations. I will follow the path where I assume I cannot control group_concat_max_len, not even in session scope, and show an SQL solution, dirty as it is, to overcome the GROUP_CONCAT limitation, under certain conditions. [...]

  • Implicit casting you don’t want to see around

    July 7, 2010

    In Beware of implicit casting, I have outlined the dangers of implicit casting. Here’s a few more real-world examples I have tackled: Number-String comparisons Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus: mysql> SELECT 3 = ’3.0′; +———–+ | 3 = [...]

  • SQL: good comments conventions

    July 1, 2010

    I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code. I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate. Table definitions The CREATE TABLE statement [...]

  • SQL: forcing single row tables integrity

    June 22, 2010

    Single row tables are used in various cases. Such tables can be used for “preferences” or “settings”; for managing counters (e.g. summary tables), for general-purpose administration tasks (e.g. heartbeat table) etc. The problem with single row tables is that, well, they must have s single row. And the question is: how can you force them [...]

  • Verifying GROUP_CONCAT limit without using variables

    June 10, 2010

    I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here. Normally, I would simply: SELECT @@group_concat_max_len However, I am using views, where session variables are not allowed. Using a stored function can do the [...]

  • Choosing MySQL boolean data types

    June 3, 2010

    How do you implement True/False columns? There are many ways to do it, each with its own pros and cons. ENUM Create you column as ENUM(‘F’, ‘T’), or ENUM(‘N’,’Y’) or ENUM(’0′, ’1′). This is the method used in the mysql tables (e.g. mysql.user privileges table). It’s very simple and intuitive. It truly restricts the values [...]

  • Views: better performance with condition pushdown

    May 20, 2010

    Justin’s A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer’s site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with [...]

  • Discovery of the day: GROUP BY … DESC

    May 4, 2010

    I happened on a query where, by mistake, an SELECT … ORDER BY x DESC LIMIT 1 was written as SELECT … GROUP BY x DESC LIMIT 1 And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It’s documented. In MySQL, GROUP BY [...]

  • Proper SQL table alias use conventions

    March 11, 2010

    After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query. I’m going to leave formatting, upper/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I [...]

  • Tip: faster than TRUNCATE

    March 9, 2010

    TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass. TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the [...]

 
Powered by Wordpress and MySQL. Theme by openark.org