… 10 or so characters usually are the same). Of course UNIQUE index optimizations can no longer be applied either. A: This… case. Some people would prefer to define second key as UNIQUE this has benefits and drawbacks. Benefit being you can get…? A: If you extend the index from (vendor_id) to (vendor_id,order_id) you will make it 4 bytes longer (assuming order…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… order) the primary key you specify (an auto_increment), a unique key, or one it creates for you. It’s been… implications. Remember that if you don’t create either a unique key or a column with an auto_increment primary key… returned. SELECT * FROM articles INNER JOIN users ON users.id = articles.user_id AND users.featured = 1 ; > Use Indexes This item was…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… InnoDB statistics (such as IO read operations, the number of unique pages the query accessed, the length of time query waited… # Files: slow-query.log.1 # Overall: 20.08k total, 167 unique, 16.04 QPS, 0.01x concurrency ________ # Time range: 2011-12… JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…_NAME: Bar COLUMN_NAME: X ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: test REFERENCED_TABLE_NAME: Foo… = ‘order_id‘, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, REFERENCED_TABLE_SCHEMA = ‘ecommerce’, REFERENCED_TABLE_NAME = ‘Orders’, REFERENCED_COLUMN_NAME = ‘ID‘; All…
Comment: Some little known facts about Innodb Insert Buffer
December 14, 2010 By 12 Comments
… locking problem with unique keys when inserting CREATE TABLE `male_id` ( `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `jy_id` int(10) unsigned NOT NULL, PRIMARY KEY (`entry_id`), UNIQUE KEY `jy_id` (`jy_id… key (entry_id), the problem remains. But the problem is gone if I change the unique key (jy_id) to be index (jy_id).
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 45 Comments
…, is just a copy of the PK column comment_ID with a unique index: mysql> show create table wp_comments\G *************************** 1. row… ’0′, `rajid` bigint(20) unsigned default NULL, PRIMARY KEY (`comment_ID`), UNIQUE KEY `rajidx` (`rajid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row… if it is not a unique index. For instance: mysql> explain select count(*) from wp_comments; +—-+————-+————-+——-+—————+———————-+———+——+——–+————-+ | id | select_type | table | type | possible…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… is quite frequite to see something like PRIMARY KEY(id), UNIQUE KEY id(id), KEY id2(id) The logic I heard behind this often – create primary key as object identifier, now we create UNIQUE because we want it to be UNIQUE and we create KEY so it it…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
September 4, 2011 By 48 Comments
… the table. CREATE TABLE `test` ( `id` int(11) NOT NULL, `md5` char(32) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `md5` (`md5`) ) ENGINE… which row caused the dupe error. (I guess the unique row_id would not be of much help, so only return the…?) Well this is because i want “on duplicate key return unique_row_id” Cheers //Steve
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
July 1, 2010 By 48 Comments
…_KEY(`id`, `timestamp`) == a single key (but with two values, thus more unique) The `id` is very likely always going to be unique. However, adding the `timestamp` to the primary key makes it even more unique; so, if the `id` exists in…
Post: Pacemaker, please meet NDB Cluster or using Pacemaker/Heartbeat to start a NDB Cluster
May 19, 2010 By 10 Comments
…_attributes>
