… a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, …t the primary key solution for random selection only work when the IDs for movies are distributed … and reinitializing the sequence after making certain insert/update/delete operations. If you have …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…could easily be quicker on a partitioned table, as Mysql can fire off one thread per partition (at … saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do with … quicker than deleting a few hundred thousand rows… for example, if you need to delete. However, thank …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`. However it will interesting…/insert-roll-2, command line to run: sysbench –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql…-rel30.2-500.Linux.x86_64/lib/mysql/libjemalloc.so [mysqld] gdb datadir=/mnt/data/mysql #for SSD innodb_flush_neighbor_pages = none…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always opens all partitions, even before thinking about… you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non… case for PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third use case for…
Comment: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 2, 2013 By 10 Comments
… (and by extension, safer) to run (CREATE TABLE AS SELECT *, UPDATE host, INSERT) a few times than trying to recreate the grants… to move the mysql db to InnoDB tables (try googling for that), but it would be a nice addition… Thanks for the log…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method …solve this problem for INSERT IGNORE? As I informed you before, it is not documented that INSERT IGNORE creates … the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc …go back to our customer. 1500 INSERT ODKUs per second, sustained for 24 hours per day. The …we try an INSERT … ODKU? First, recall what’s in our table: (root@localhost) [test]> select * from update_test; …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME…,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… range for unsigned column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select… for values over 2bil as data continued to be inserted the system essentially collapsed in matter of hours. Thank you, Oracle team, for…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1… to be decompressed. Third, the COUNT acts as RLE compression for the other attributes. Don’t use FLOAT with this method… (0.00 sec) mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select a.val, b.val…
