MySQL

Part of the LAMP platform.

Tips

date/time

To group on weeks or months (or hours) use date_format() - for weeks use %V instead of %Y
to add/subtract date intervals: date_add(<value>, interval <n> days), date_sub()
difference between 2 values:
- datediff(<bigger>, <smaller>)
- timediff() (returns time values - which you can wrap with truncate(time_to_sec())
- timestampdiff(<unit>, <smaller>, <bigger>) (returns integer)
  - if you're going to do a group-by/sum on elapsed time, use second as your interval and /60 or /60/60, to avoid rounding error (it returns integers)
to manually group values of a field into buckets, use CASE/WHEN
when parameterizing ad-hoc queries with variables, improve performance by casting variable as same type as column: set @lastName=CONVERT(CAST('Smith' AS CHAR(32)) USING ASCII);
to reference JSON piece: use column->path like data->"$.key"

correlated subquery (subquery takes field value from outer query as a param): using exists

But doesn't support transactions and other "advanced" features. For that you typically use PostgreSQL.

but few websites really use/need transactions, and Scalability is pushing people away from those features.

Do FederatedTables allow greater scale-out of a schema that uses foreign keys, etc.?

installing on MacOs X

Security concerns often keep you from using such things (because you make your db as inaccessible as possible, except from other machines behind that firewall)

batch input/output

date queries: MySQL doesn't have as nice date_trunc and date_part functions as PostgreSQL.

you can use date(timestamp) to group by whole-date
there's a month() function but that gives you just the month-number, so doesn't work for cross-year queries
- though I suppose you could break on year() and month()

monitoring tools (recommended by friend May'2011)

slow query log http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
- Percona allows you to get below 1 second minimum http://www.percona.com/docs/wiki/percona-server:features:slow_extended_51?redirect=2
  - Percona also particularly helpful in MultiCore servers
Running "mysqladmin processlist > "/tmp/mysqlplog" ; sleep 5" in the background
Zabbix
mytop http://jeremy.zawodny.com/mysql/mytop/
spotlight http://www.quest.com/spotlight-on-mysql/ - run it for an hour to collect data then turn off - adds load to server
MySQL Tuner - could help diagnose if your mysql instance is configured/tuned correctly for your workload http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
If you're running innodb storage engine, innotop will give you 'top' style real-time view into your databases http://innotop.googlecode.com/svn/html/index.html
Maatkit would let you look at all aspects of your instance including slow query logs http://www.maatkit.org/

Bill Seitz