… in a table using InnoDB, the database will create one for you that you can’t use or see. … called Not using > Join. This is typical error. On other hand in MySQL you might be > better of using several … as fixed-width data (think of converting your varchars to chars for example), then having to do …
Comment: Using CHAR keys for joins, how much is the overhead ?
June 19, 2007 By 18 Comments
…join speed from 6.1 to 4.4 seconds for Innodb tables. Using…varchar(10) to varchar(255) in my “short” varchar join tests for Innodb table with utf8 encoding. This caused some degradation slowing down join…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… is it stored in a VARCHAR(36)? When tables get big, every byte matters a lot. Use the smallest data types… InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL server…-value tables in a complex query with many joins. I have never seen this approach scale well. Use The Best Technologies You Can MySQL…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL…`,`val`) ) CREATE TABLE `dim` ( `id` int(10) unsigned NOT NULL auto_increment, `pad` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) mysql> select count…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
…varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join…mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… those two extremes: mysql> create table params ( view_name varchar(250), param1_val int,…use the MERGE algorithm (1 warning): create alorithm=merge view v2 as select c1, count(*) from v1 join… params table. Place parameters into the params table using the REPLACE statement: mysql> replace into params…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…varchar(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; Last fields starting with “Div*” are not really used…mysql -S /tmp/mysql-ib.sock -e “LOAD DATA INFILE ‘/data/d1/AirData_ontime/${YEAR}_$i.txt.tr’ INTO TABLE… carrier) t JOIN (SELECT carrier, …
Comment: Air traffic queries in InfiniDB: early alpha
November 3, 2009 By 19 Comments
…InfiniDB, a varchar(250) column is no more wasteful than a varchar(10) column, but not using that… with the MySQL information schema and we currently report 2000 rows for every table. This one… does not take responsibility for aggregation and join steps, only scans and filters. So specifically,…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… be treated differently. For uncompressed index blocks MySQL can do binary search inside the page – …but not integer keys is the biggest reason joins using string keys is so much slower compared …keep it simple: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL , `c` varchar(20) NOT NULL default ”,…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
… string with all multi-byte characters into a VARCHAR(500) column, that value will also be chosen … you to rewrite as joins queries that access all columns in the original table. It also requires you … if you choose XML as format and you use MySQL‘s built in functions for handling this data. …
