… recently posted an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this a… insert benchmark for this case with larger data size so here they are: I’ve created MyISAM tables containing just integer auto_increment primary key… 200 rows/sec and the it is still slowing down a bit as key file growths. So in this little case we…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…’t create either a unique key or a column with an auto_increment primary key in a table using InnoDB… how to create indexes. Foreign key references may add processing time to inserts, updates, and deletes but… queries, especially > if you see them in slow query log. If you have trouble understanding >…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT… to tmp table” part will not be affected by a slow tmpdir, but rebuilding the indexes will obviously take longer. Another…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…| | closing tables | 0.000003 | | freeing items | 0.000007 | | logging slow query | 0.000002 | | cleaning up | 0.000002 | +———————-+————+ 17 rows…(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null);…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… pretty tricky, and all that extra network traffic would really slow down transactions. You may have already guessed, but Galera does… to test on and insert some data: node2 mysql> create table autoinc ( i int unsigned not null auto_increment primary key, j varchar(32) ); Query…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… great to be avoided. Long primary key – why did not I use auto_increment id in this case ?… primary key This just comes as an effect of choosing url as the key. It surely makes inserts…slow as it is quite fragmented. Table however is not getting any table scans just single row lookups by primary key…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… than useless they’re detrimental. Not only do these indexes slow down insert operations, the MySQL optimizer sometimes appears to become rather… table create table orders( order_id int unsigned not null auto_increment primary key, item_type_id tinyint unsigned not null, locale_id smallint…
Comment: ORDER BY ... LIMIT Performance Optimization
December 29, 2008 By 100 Comments
… auto_increment, a integer, b integer, primary key(id)); > insert into c (a,b) value (1,2),(5,6),(3,9),(122,22),(32,54); > insert… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——+———-+————-+ | 1 | SIMPLE | c | index | NULL | PRIMARY | 4 | NULL | 20971520 | Using index… I use indexes, the paginationing on huge table is very slow 
Any idea why? My MySQL is 5.0.51a-18…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… range scans on what would be primary key but it also can slow down your inserts and make primary key significantly fragmented. I also would note… times. Q: In which cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should pick something else if…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET… a lot of manual work and thus slow There is no way to differentiate tables…this pointer is lost when new record is inserted. But the actual old values remain in…
