… you can get extra optimizations by optimizer knowing index is UNIQUE the drawback is insert buffer will not be able to be… it also can slow down your inserts and make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Comment: Innodb Performance Optimization Basics
April 29, 2008 By 75 Comments
… of insert/update operations per day (planned). The web client that reads from the database requires several indexes, however having those indexes slows down the insert/update operations. Is it advisable to set up replication such that the MASTER database does not have any indexes… much of a difference does it make to have the MySQL server on a dedicated machine? Is there an article that…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… not use indexes even if they take less time (–log-queries-not-using-indexes) Slow query log is great to spot really slow queries… INSERT IGNORE INTO articles (id, body,)VALUES(3558268,’sample text’); Filtering Slow Query Log – Especially after the changes to log all queries slow… example adding indexes often help SELECT queries but slow down INSERT/UPDATE ones. Final Note: You do not have to have patched MySQL for these…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… indexes on the referencing columns are quite useless. Actually they’re more than useless they’re detrimental. Not only do these indexes slow down insert operations, the MySQL optimizer sometimes appears to become rather overwhelmed by the number of indexes and tables to choose…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…slows down about 2x. What is going on here ? To understand it we should take a look at the buffer pool contents. mysql…_index as p using(index_id) where t.schema=’test’ group by i.index_id \G INDEX…system. It is worth to note MySQL actually uses midpoint insertion for its buffer pool replacement …
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 12 Comments
… property allows insert buffer to survive transaction commits and even MySQL restarts. Really it may take weeks before the given index page is… in insert buffer the merge is performed before page is made available. This means insert buffer can slow down read operations. The other way insert…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… and all that extra network traffic would really slow down transactions. You may have already guessed, but …`) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) node2 mysql> insert into… 0 page no 823 n bits 72 index `PRIMARY` of table `test`.`autoinc` trx id …
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially…, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process. Mark Callaghan and…
Comment: Dropping unused indexes
January 15, 2009 By 6 Comments
Pat, Unused indexes are dead weight. How much it hurts it is application … of it. Unused indexes slow down inserts but they also hurt selects because they waste time in caches. They also make MySQL optimizer to consider…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… but as it progresses it may slow down dramatically, typically if Index BTREE has to be built by insertion and data does not fit… There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE… makes it extremely hard to predict. Especially fact load can slow down dramatically as amount of data loaded in the table increased…
