… time, I wrote about how I measured the performance on a join between a few tables…the alternative: table-scan the fact table, and do index lookups in the two dimension tables. MySQL doesn’…becomes a table scan of about 313 sequential reads (150 million rows / 117 bytes per row / 4096 bytes per read),…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… examples to mention: per-user CPU time usage, some queries may be CPU bound rather than reading lots of rows support for prepared statements which all currently fall under Other_commands a few MySQL performance counters broken down by users (full joins, table scans…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… using MySQL to using MySQL + Memcached…tables each contain 5M rows of data. let’s see what happens: You can see a dramatic drop off in performance… scream other times it won…scanning and do not have enough memory for memcached. For instance, if you have 1,000,000 rows… “transactions†per second for…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… tool for MySQL Performance optimization. If you…rows is estimated performing btree dive with certainly structured tree you still can get number of rows off 3-10 times…table scan of table in memory may be done at speed of millions of rows per second, while retrieval of large rows from uncached table…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…rows per second it will be able to do? CREATE TABLE `m` ( `i` int(11) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql…row scan speed is significantly affected. I also decided to see how longer rows affect performance and created the following table: CREATE TABLE…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table…performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table scan performs about 25 times better than accessing rows…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…time their queries and to catch those which run less than a second as they can also be a performance… usage statistics. MySQL currently allows you to see many per-session statistics …time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 # QC_Hit: No Full_scan: No Full_join: No Tmp_table…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
…tables instead. The process completed in 12 seconds for 1000 rows in a batch vs 40 seconds for single row…kind of performance bug of 5.0. At row0sel.c:row_search_for_mysql() “PHASE 3:”, InnoDB judge whether the SQL is “SELECT” or not. 5.0 does scanning the SQL for each time…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…scan…performance win, perhaps. Sometimes it is. Other times, it is a major performance killer. It’s fairly straightforward to tell when MySQL…rows, and an EXPLAIN produced the following execution plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE table…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE…time now set to 1000 making Innodb Buffer Pool Size scan…
