elephpant on rails

MySQL: BLOB vs TEXT

The only difference between the BLOB and TEXT types is that:

• BLOB types store binary data with no collation or character set,
• TEXT types have a character set and collation,

DISTINCT vs GROUP BY

Although it may seem so, they do NOT do exactly same thing.

While GROUP BY groups results only by those columns that are explicitly listed after the clause, DISTINCT groups results by ALL columns that are present in SELECT statement (and not only by the one it precedes).

So following queries

SELECT DISTINCT article_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id
return same results,

but

SELECT DISTINCT article_id, author_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id
return different results,

though

SELECT DISTINCT article_id, author_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id, author_id
again return same results.

As a sidenote, with GROUP BY you have HAVING clause by your hand as well.

Avoid temporary table when using MySQL’s ORDER BY

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.

More tips like this in manual’s WHERE Clause Optimization.

MySQL “Using index” in EXPLAIN’s extra column

When you see Using Index showing up in Extra column of EXPLAIN output, it means a covering index is used, it is MySQL is able to locate every field for a given table within an index without lookups to table itself.

It’s simply another reason to re-consider using asterisks (*) in SELECT statements.

Simplest example:

mysql> EXPLAIN SELECT * FROM people;
+----+-------------+--------+------+-...-+-------+
| id | select_type | table  | type | ... | Extra |
+----+-------------+--------+------+-...-+-------|
|  1 | SIMPLE      | people | ALL  | ... |       |
+----+-------------+--------+------+-...-|-------+

mysql> EXPLAIN SELECT people_id FROM people;
+----+-------------+--------+-------+-...-+-------------+
| id | select_type | table  | type  | ... | Extra       |
+----+-------------+--------+-------+-...-+-------------|
|  1 | SIMPLE      | people | index | ... | Using index |
+----+-------------+--------+-------+-...-|-------------+

It can be also very handy when using joins, and don’t really need data from one (or more) of joined columns.

For me more stuff like this see Jay Pipes “Target Practice: A Workshop in Tuning MySQL Queries” (OSCON 2007).

MySQL fast data load