June 18, 2013

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… would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first…”: * If the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1…

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

Post: Predicting how long data load would take

May 24, 2007 By 14 Comments

… can build non-unique indexes by sort which is very fast and Innodb can use insert buffer for them. Data Insert Order It… to be set for optimal load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort… not by BTREE insertion. For Innodb SET UNIQUE_CHECKS=0 may be used to speed up load to tables with unique keys. So as…

Filed Under: Insight for DBAs Tagged With: ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…much less fragmented indexes because records are inserted in the correct order into sequentially allocated…in the MySQL manual: “… you can generally speed the overall process of creating and loading…is not applicable: UNIQUE indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…, which discusses improving bulk insert performance. At the very bottom, the page claims that you can speed up bulk loading into an… as they suggest, your data won’t load due to unique key constraint violations unless you also do something to provide…_state | 0 | +—————————+——–+ 10 rows in set (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full…

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

Post: My Innodb Feature wishes

May 4, 2006 By 30 Comments

… space compression. Insert buffer for delete. For Insert operation Innodb uses nice tecnique called Insert Buffer which speeds up inserts for non-unique indexes a lot… times+ slower, which is especially bad for rollback of failed inserts which may take too long to complete Index build by…

Filed Under: Insight for Developers Tagged With:

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… so that we can see the full write speed of the disk for throughput. INNODB uses fuzzy… you’re performing INSERT with multiple values? We build up INSERTS on our clients and insert values 50 or … key not reference an entire primary key or unique constraint (something that is mandatory in every other…

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… UNIQUE this has benefits and drawbacks. Benefit being you can get extra optimizations by optimizer knowing index is UNIQUE the drawback is insert… be primary key but it also can slow down your inserts and make primary key significantly fragmented. I also would note… on vendor, will the index have any impact on the speed of the query? A: If you extend the index from…

Filed Under: Insight for DBAs, MySQL

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… would not > expect it to beat MySQLi in speed. It is however bad idea to use > mysql_ …Remember that if you don’t create either a unique key or a column with an auto_increment …indexes. Foreign key references may add processing time to inserts, updates, and deletes but there are other benefits. Also …

Filed Under: Insight for Developers Tagged With: , ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

…tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE… much larger than table itself) The index build speed is however not only reason I can’t …simple and same for all insert cases – in fact if the insert is single value insert or the number of …

Filed Under: Events and Announcements Tagged With:

Post: Conflict Avoidance with auto_increment_increment and auto_increment_offset

January 12, 2011 By 2 Comments

… these options help to avoid some conflicts – if you have insert in table with auto_increment column you will almost for… – updates to the data, deletes as well as insertion to the table with unique keys and non auto increment primary keys. The… logs but having it in the database too allows to speed up process in many cases.

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts