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
| Share |
- 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.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
Artem, I saw your comparison of hash and btree, you might be interested in scaleDB they use a different indexing model and it is both smaller and faster than btree. It is also a multi-table index that incorporates the relationships between tables.