beer planet

• Jay Pipes, Tobias Asplund
• Finding out the number of rows that would have been returned (MyISAM and InnoDB)
• SQL_CALC_FOUND_ROWS and FOUND_ROWS()
• COUNT(*)
• MEMORY table
• if query cache is on, then it makes no difference
• if it's off
• Memory MyISAM is fastest
• FOUND_ROWS() is slightly slower than count(*)
• more in the slides that I'll add later
• quite a lot of humor, these guys are fun
• query union vs index_merge union
• SELECT … WHERE a UNION SELECT … WHERE b
  vs
  SELECT … WHERE a AND b
• index_merge wins
• composite index vs index merge
• composite index is faster
• of course, multiple indexes are more flexible than composite index
• sort union vs composite index
• …
• unix time (int unsigned) vs datetime
• query cache disabled
• 100k rows
• now and 20 years back
• retrieval range between 50 and 365 days
• all data fits into memory
• UNIX_TIMESTAMP is worst
• DATETIME in the middle
• strtotime() fastest
• is BENCHMARK() a good simulation test?
• NOW()
• MONTH(NOW())
• MONTH('YYYY-MM-DD')
• DATE_FORMAT()
• SUBSTRING()
• all about the same if php benchmark script is used
• BENCHMARK() is very unreliable, the results vary a lot
• X in Y how many times?
• 3 different methods on mysql forge
• 2 UDFs and 1 using native functions
• query cache off
• 1K rows
• all rows have "/"
• count the number of "/"
• Marcelo's solution "COUNT_CHAR_OCCURRENCE": 4th place
• Garrett Hill's solution "countstring" (it's technically slightly wrong, because it doesn't account for multibyte characters): 2nd place
• Roland Bouman's solution using mysql functions: 1st place
• Jay's way: function in php: 3rd place
• MEMORY engine HASH index vs BTREE index
• BTREE is faster
• HASH uses less memory

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.