…that had single-column indexes on them, MySQL could sometimes make use of the multiple…status` (status), INDEX `user_type` (user_type) ) ENGINE=InnoDB; SELECT user_id FROM user WHERE … *************************** id: 1 select_type: SIMPLE table: users type: ref possible_keys: user_type key: user_type…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… how predictable I am sometimes, isn’t it?) mysql> pager less mysql> show innodb status\G For big result sets, it’s a pretty… there are any lock waits: mysql> pager /tmp/lock_waits PAGER set to ‘/tmp/lock_waits’ mysql> show innodb status\G ——- TRX HAS BEEN… lookup | key actor->PRIMARY | possible_keys PRIMARY | key_len 2 | ref sakila.film_actor.actor_id | rows 1 +- JOIN +- Bookmark lookup…
Comment: Yasufumi Kinoshita joins Percona
November 3, 2008 By 8 Comments
…the MySQL/InnoDB ecosystem. Yasufumi has written several patches to improve InnoDB‘s scalability and has tracked down a serious bug in InnoDB-…=&metrics_ref=&affectscustomer=&mysqlnetwork=&qatestneeded=&qareviewneeded= We are today working towards the release of InnoDB Plugin 1.0.2. The InnoDB …
Post: What is stored InnoDB buffer pool
September 28, 2008 By 8 Comments
… – we made a short patch for MySQL 5.0 which show innodb buffer pool content mysql> select * from information_schema.INNODB_BUFFER_POOL_CONTENT… | | 49 | 0 | 48 | 0 | 0 | 0 | 0 | 51 | 17855 | index | REF_IND | | SYS_FOREIGN | | 50 | 0 | 49 | 0 | 0 | 0 | 0… download P.S. There is Jeremy Cole’s patch for InnoDB plugin MySQL 5.1, and main idea was taken from there.
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…MySQL flavors. MySQL 5.5.24 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=512M innodb…key | key_len | ref | rows | filtered | Extra | …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… MySQL gets a “row pointer” which can be primary key value (for Innodb…| table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ | 1 | SIMPLE | idxtest | …
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… performance of your queries. Take the following example, using the InnoDB storage engine: CREATE TABLE `t1` ( `char_id` char(6) NOT…_len: 18 ref: func rows: 1 Extra: Using where; Using index 2 rows in set, 1 warning (0.00 sec) mysql> SHOW…: 18 ref: test.t1.char_id rows: 1 Extra: Using index 2 rows in set, 1 warning (0.00 sec) mysql> SHOW…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…is also interesting to note performance of Innodb tables in this case: for VARCHAR … PRIMARY,name key: name key_len: 40 ref: const rows: 1 Extra: *************************** 2. row *************************** id…join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c….
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——-+——+—————+——+———+——-+——+———-+————————–+ | 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | …) engine=innodb; Query OK, 0 rows affected (0.10 sec) Notice that MySQL…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… to benchmark. The results below are for MySQL 5.1.18 using MyISAM and Innodb tables. This time unlike other benchmarks I… type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row *************************** id: 1 select_type… is to convert Innodb table to latin1 character set. I was expected this to shorten some internal buffers MySQL has to allocate…
