…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…for MySQL 5.6, increase of 0.02s. But the query times for MariaDB 5.5 are greater than both MySQL 5.5 and MySQL… improvement and changes needed in the optimizer’…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use… 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… this example as illustration no recommendations are good for every case and you need to check what is important in your case…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…. How can we do that? Quality Control Queries We need to check for orphaned rows in every parent-child relationship in your… about both primary keys and foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… some sort of garbage collection. Support for Delayed Invalidation Many MySQL application do not need absolutely up to date data, something … for rather large set of applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…for one or two capabilities you absolutely need now; then add the rest later, reloading the data if needed. Aggregate Aggregation is absolutely key for…re doing this with vanilla MySQL, you will need to aggregate your data. … other than the primary key, we can shrink the primary key‘s width: create …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…directory with some subdirectories inside, one for every single index in the table: …need to identify which of those two indexes is the Primary Key. This is our next step. 2- Identifying the Primary Key…3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where emp_no=10008;…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…primary key for each complete key value. So when you have index (a) and id is a primary key…, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql…things going you will need to use FORCE INDEX(a) to force MySQL optimizer using right…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…PRIMARY KEY (`airport_id`), KEY `CityName` (`CityName`), KEY `State` (`State`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Data from BTS ontime flight data. Data for… For large datasets effective compression results in the need for fewer…socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno….
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…patterns: primary key single row lookup, primary key range lookup, same access types for primary key and…MySQL version We used MySQL 5.1.16-beta sources for MyISAM / InnoDB and pbxt-0.9.85-beta.tar.gz Compilation parameters: For…of InnoDB clustering by primary key. InnoDB does not need an extra lookup…
