Open Source Relational Data Base engine. Known for simplicity and speed.

Part of the LAMP platform.



  • 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);

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

old notes

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

Command Line interface: 'mysql'

GUI tools

  • 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

  • output/export: "mysqldump" from Command Line
  • import: "source" from inside mysql

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)

Edited:    |       |    Search Twitter for discussion