…INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL DEFAULT 0, username VARCHAR…rationale behind it out the door. The data change so much such that the index hint… learn here? Just because the output from EXPLAIN “appears to look good” doesn’t mean …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… – this index type is very good to be able to quickly lookup the data on …` ( `i1` int(10) unsigned NOT NULL, `i2` int(10) unsigned NOT NULL, `val` varchar(40) …Changed: 10900996 Warnings: 0 Lets see what happens in this case mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from…*************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_…to read row data from OS Cache. So what if we convert i and j columns to varchar while sticking to… compared to Joining on Int, for…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…TABLE t(id INT AUTO_INCREMENT PRIMARY …m extending the VARCHAR column to trigger … | Op | Msg_type | Msg_text | +——–+———-+———-+——————————————————————-+ |…changes. So adding them back with a separate ALTER TABLE after restoring the data from…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…similar to this: SELECT name FROM t1…id: 1 select_type: SIMPLE table: t1 type: eq_…int(11) unsigned NOT NULL auto_increment, `val` int(11) unsigned NOT NULL, `name` varchar(100) NOT NULL, `t1_id` int…for such data distribution …changes done in MySQL 4.1 As Monty explained us in MySQL 4.1 the change to…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…int unsigned NOT NULL auto_increment, `char_id` char(6) NOT NULL, `v` varchar…data set fit easily in memory. mysql> EXPLAIN EXTENDED SELECT SQL_NO_CACHE COUNT(t1.char_id) > FROM…1 select_type: SIMPLE table: t1 type: …change resulted in more than a 25% improvement in average execution time, from 4.33 to…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from…int(11) NOT NULL, `l_partkey` int(11) NOT NULL, `l_suppkey` int(11) NOT NULL, `l_linenumber` int…comment` varchar(44…| select_type | table | type | …to scan rows and then did access to data to…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
… example ( id INT UNSIGNED NOT NULL…into a VARCHAR(500) column,…to change code in a lot of places. How much you can win with this approach depends a lot on the type of data…from using SQL operators/functions directly on the individual fields you want to store. It also forces you to read/write more data…
