1

MySQL Conference Liveblogging: MySQL Performance Under A Microscope: The Tobias And Jay Show (Wednesday 2:00PM)


Posted by Artem Russakovskii on April 16th, 2008 in Databases

  • 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 and blogger. 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.

Tags: , , , ,