…benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here…slower in new MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * …during execution of query, as MySQL used index to scan rows and then …247.27 0.30 0.55 0.55 29.67 dm-0 0.00 0.00 550.10 0….
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…; Using index 2 rows in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select sql_no….33 to 3.12 seconds. This test was performed with MySQL 5.0.67, FreeBSD 7, on a box with 2GB RAM.
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
… 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 70748 and INDEX/TABLE statistics: mysql> select * from information_schema.index_…0.01 sec) If you would like to test it here is link to patch for 5.0.67. http://www.mysqlperformanceblog.com/mysql…
Post: New patches, new builds
September 5, 2008 By 24 Comments
…INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_INDEXES…
Post: What is stored InnoDB buffer pool
September 28, 2008 By 8 Comments
…MySQL 5.0 which show innodb buffer pool content mysql> select…0 | 0 | 0 | 0 | 3 | 5 | unknown | | | | | 2 | 0 | 3 | 0 | 0 | 0 | 0 | 4 | 6 | unknown | | | | | 3 | 0 | 2 | 0 | 0 | 0 | 0 | 5 | 3 | inode | | | | | 4 | 0 | 4 | 0 | 0 | 0 | 0 | 6 | 17855 | index | CLUST_IND | | SYS_IBUF_TABLE_0 | | 5 | 0…
Comment: Multiple column index vs multiple indexes
August 25, 2008 By 13 Comments
… size. The index cache was set at 4 GB. Mysql version 5.0.67 ( i was doing testing for bug 29857). mysql> show index from… 2 rows in set (0.00 sec) mysql> select count(*) from t1000; +———-+ | count(*) | +———-+ | 18000000 | +———-+ 1 row in set (0.00 sec) (the…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
… take tpcc schema and running standard MySQL ® 5.0.83, and assume we want …: 2 indexes are detected. InnoDB: Progress in %: 1 2 3 4 5 6 … 62 63 64 65 66 67 68 69 70 71 72 …100 done. When finished quick check mysql> select count(*) from order_line; +———-+ | count(*) | +———-+ | 32093604 | …
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…Select_commands: 1759 Update_commands: 254 Other_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql….PRIMARY | 67 | …5 | | hosting2.wp_usermeta.user_id | 413 | +——————————————–+———–+ 11 rows in set (0.00 sec) The statistics for tables and indexes…
Comment: ORDER BY ... LIMIT Performance Optimization
September 20, 2006 By 100 Comments
… with Jay’s point (1st post). I’m using MySQL 5 and am having some fun adapting to their new …I have a table that I use a full text index on when I run a query with no order …url, stripped) AGAINST (‘test’ ) DESC LIMIT 0, 50 27 rows fetched (60.67 sec) id select_type table type possible_keys key …
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
…indexes: – load of data is getting slower; – to design proper indexes is additional research, especially when MySQL…SELECT carrier,totalflights,ndelayed,ndelayed*1000/totalflights as c3 FROM (SELECT carrier,count(*) as totalflights,sum(if(depdelay>10,1,0…67 For reference I used 5.1.36-Tokutek-2.1.0 …
