… for which this tool was originally built is long over, but still the tool serves well … NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For… following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer… use Scale Factor of 40 (InnoDB dataset size ~95G), because the query was taking far too long to execute, ~11 hours in…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys… for long primary keys but usually it is not major. Non Sequential primary key This just comes as an effect of choosing url as the key…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… in their own segments. Clustering. I love Innodb‘s ability to cluster data by primary key it is so helpful in many cases… inconvenient. Having independent CLUSTER=key option would be great, it also would help to avoid penalty of very long primary key – you can simply select not to cluster by it instead of creating fake primary key and promoting real…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… Choose primary keys very carefully, especially with InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming… capacity. What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order rows are stored in… levels of aggregations. Watch Out For The Long Tail People talk about the long tail and how you can focus on optimizing…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… way too long for your 150 GB table! If you have inserted in an ascending order of the PRIMARY KEY, then InnoDB should have… used for primary key lookups or not showed up in “show innodb statusâ€. select * from table where id=5 show innodb status: Hash… searches/s, 15979.01 non-hash searches/s HT: Strange, primary key equality searches should work through the adaptive hash index! What…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…lose concurrency and performance. The problems are even worse with long running queries like INSERT INTO table1 … SELECT … FROM table2. …AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No gaps…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
….18 using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not on primary key and have query… for Innodb tables MyISAM Tables may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… Innodb can use insert buffer for them. Data Insert Order It is well known it is important to load data in primary key order into Innodb tables for optimal performance, but effect does not end… we loaded data in parallel and our rows were relatively long. In most cases I would expect load speed to be…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… will not produce deadlocks for Innodb you should always see deadlocks as a probability as long as you write to the database… may not work for Innodb and vice versa. You may benefit from different index structure for Innodb, including different primary key setup, as well… row by row to avoid holding table lock for long time in Innodb you want larger updates to reduce cost of transaction…
