…is rather small table which fits in memory in all cases (and dictionary table does too…is quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL… possible_keys: PRIMARY key: PRIMARY key_…index (for the case when data fits in memory!) So, if you have an…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… is designed such that this is not the case. Updates and deletes of primary key values are instead restricted – that is not permitted. So the indexes… do so. Here’s an example table create table orders( order_id int unsigned not null auto_increment primary key, item_type_id… in MySQL too and writing a few hundred of those isn’t going to be to enjoyable at all. Additionally I need an index…
Post: Percona Toolkit 2.1.9 is ready
February 15, 2013 By Leave a Comment
… false-positive error “Cannot nibble table because MySQL chose no index instead of the PRIMARY index” This is an important bug fix for pt-table-sync… “DROP PRIMARY KEY“, and you have to specify –no-check-alter to acknowledge this case. * Fixed bug 1099933: pt-stalk is too verbose, fills…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…primary key(day, ad, is_blue) ); What can we improve here? Especially assuming that there are indexes other than the primary key, we can shrink the primary key…is clearly better for some workloads out-of-the-box than MySQL is, especially for more complex queries. Percona is not tied to MySQL…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…index entries being sorted by primary key for each complete key value. So when you have index (a) and id is a primary key the real index is…PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…MySQL. There are two solutions here. Memlock and O_DIRECT. Memlock is apparently unstable on Linux. O_DIRECT is an…ve seen in high IO scenearios is that it commits too often and we’re only … in an index. I think it is sound design practice to make the foreign key to reference an entire primary key,…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name)… find the index_id. Either because too many matches or there is no …mysql/ibdata1 15 0-18 0-19 # Apparently 0-18 is PRIMARY index and 0-19 is index idx_actor_last_name. It is not guaranteed though an…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… The storage worker currently logs too much information. This can …flatfiles 30GB ext3 EBS This is an ext3 volume which contains …indexes (not even primary keys). Here is the complete InnoDB schema from one shard. The schema is…3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock …
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…is somewhere in between those two extremes: mysql> create table params ( view_name varchar(250), param1_val int, connection_id bigint, primary key…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… size : 1024 KB 4GB of RAM MySQL version We used MySQL 5.1.16-beta sources for MyISAM /… clustering by primary key. InnoDB does not need an extra lookup to access to data. READ_PK_RANGE_INDEX Query: …many cases the transition from 16 threads to 64 is too hard for InnoDB even with all scalability fixes…
