elephpant on rails

PHP date conversions between timezones

In PHP 5.x line dates are handled properly (thanks to Derick Rethans). Even in PHP 5.3 we have loads of nice date/time improvements.

One of nice things is posibility to convert dates between timezones easily.

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).

Regexp: string followed by and preceded by

Regular expressions are full of features (who can learn it all? or read off and understand a few days after it was written?). One of them is possibility to check if desired match is followed or preceded by given pattern.

It works also in PHP.

JavaScript inspect/dump made easy

Was ever looking for JavaScript equivalent of PHP’s var_dump() or Ruby’s inspect (or p or pp or y or… etc)?

Forget it! You have it by your hand available in Chrome developer tools and Firefox’s firebug. Just type it into console and you get beautifuly browseable dump.

Chrome:

Firefox:

PDO: grouping results to indexed associative array

Ever wondered how to group database results by first column in PHP? It’s pretty simple with PDO.

PHP: accessing private members of objects of same type

From PHP manual: Objects of the same type will have access to each others private and protected members even though they are not the same instances.

And example from the manual:

PHP new ternary operator gotcha

MySQL fast data load