June 19, 2013

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

… default ’0′, PRIMARY KEY (`link_id`), UNIQUE KEY `domain_id_2` (`domain_id`,`…insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index…is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so…

Filed Under: Percona Software Tagged With: , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb…with multi-byte character sets or with ideographic languages such as Japanese, although Japanese poses its own unique…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… InnoDB FT index by declaring a column called FTS_DOC_ID at table creation time of type BIGINT UNSIGNED NOT NULL with a unique index… (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full_name), details FROM dir_test_innodb WHERE id < 500000…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO… to the number of indexes, with fast index creation the time required to …indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

… PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of events. (root@localhost) [test]> INSERT INTO update_test (username, … behavior; the manual states that our INSERT … ODKU on a table with multiple unique indexes such as this one would be equivalent …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…insert into antest_innodb select * from antest; Query OK, 245760 rows affected (54.29 sec) Records: 245760 Duplicates: 0 Warnings: 0 mysql> show index…

Filed Under: Insight for DBAs Tagged With: ,

Post: Predicting how long data load would take

May 24, 2007 By 14 Comments

… primary key order into Innodb tables for optimal performance, but effect does not end here. If indexes are built by inserts in Btree order… build indexes via sorting not by BTREE insertion. For Innodb SET UNIQUE_CHECKS=0 may be used to speed up load to tables with unique keys…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…UNIQUE index…UNIQUE this has benefits and drawbacks. Benefit being you can get extra optimizations by optimizer knowing index is UNIQUE the drawback is insert…into IN-ranges in some cases for better index usage. Q: Is the b+ tree innodb index…

Filed Under: Insight for DBAs, MySQL

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

…indexes with Innodb (with index built by sort) this is when this should be helpful the most. Q3: Does Innodb secondary Indexes…INSERT with multiple values? We build up INSERTS on our clients and insert…unique…

Filed Under: Insight for Developers Tagged With: ,

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…have to do with InnoDB?  Data clustering. InnoDB‘s primary keys…many rows as you have unique ads per day.  … fooled into calculating the wrong thing. Be Creative With Table…here? Especially assuming that there are indexes other than the primary …do it on the master with INSERT..SELECT queries, it will…

Filed Under: Insight for Developers Tagged With: , , , ,
« Older Posts