… is right. With some > exception however. For example Innodb tables do internal full table > lock… (in order) the primary key you specify (an auto_increment), a unique key, or one it creates for… traditional database background. > Things are different with MySQL. Good design will help dictate how many…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… define the index. For example it 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… is wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will enforce unique values and will be used…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… tables. pt-index-usage The previous example only works with Percona Server so with stock MySQL we should try another approach. Percona…:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`stock` DROP KEY `fkey_stock_2`; — type:non-unique If you compare these results with the previous example, there…
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 45 Comments
… count(*). Consider this example (restarting mysql each time to flush the cache, and using O_DIRECT to bypass OS caching) mysql> explain select… NULL, PRIMARY KEY (`comment_ID`), UNIQUE KEY `rajidx` (`rajid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> btw, if…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… “foo”. Notice that the table has a PRIMARY key and another non-unique key named “guest_language”–this will be important later. As… a good candidate? In this example, it is not: a non-unique key is chosen rather than the PRIMARY key. That signals that the… there are any bugs in the tool, problems with the MySQL server (privileges, etc.), or other oddities, there is a good…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… of values for various attributes, for example; or the presence or absence of … are already inconvenient limitations on some key parts of partitioning, such as maintenance …have as many rows as you have unique ads per day. Viewed this …either using MySQL 5.1′s row-based replication, or in MySQL 5.0 …
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… Details depend on storage engines, for example MyISAM can build non-unique indexes by sort which is very…and copy it back in a binary form. MySQL Configuration Different storage engines have different settings … Innodb SET UNIQUE_CHECKS=0 may be used to speed up load to tables with unique keys. So as…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… of the word. 6) determine the number of unique words. This is not projected, but you can … in only one dimension for the first example, just like the MySQL client. This will be a linear … create table dataset ( id bigint auto_increment primary key, prime_number bigint ); spread this over as many …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… auto_increment, `name` char(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM; All cities_* … faster than for MyISAM. This is great example of the case when Innodb is much …while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city …
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…key index with low cardinality and somewhat even distribution first instead of a primary or unique key even if such a key… example table create table orders( order_id int unsigned not null auto_increment primary key… and 20 operators. MySQL will create the following indexes: unique index on order_id…
