… a table improve high perfomance in select queries, but worsen perfomance in Insert, Update and Delete queries. Is it true? And if is it, how could i balance the choice??? too many indexes and high performance select or high perfomance update with few indexes??? how balance it??? I wait…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries… gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways: 1- Change…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… high performance is Query Cache performs query lookup in the cache before query parsing, so if result is served from query cache, query parsing step is saved. Queries… of queries being cached this might reduce update speed a bit. Fragmentation over time – Over time Query Cache might get fragmented, which reduces performance…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… incremental refresh method uses the changelogs collected by FlexCDC to update the snapshot to reflect the changes that have happened in….03 sec) Notice the difference in response time. Refresh method performance comparison For demonstration purposes, I did the following: Created one… Deleted 100 order lines. Refreshed both views. Compared the refresh performance and query results. The view type does not usually* affect the…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… you get say thousands of groups at the same time ? Performing such query MySQL has to build (and do lookups) for all… multiple group remains question to check with MySQL Optimizer team 
UPDATE: I just heard back from Igor Babaev saying it was….00 sec) This query looks very scary but in fact perform much better than original one. In the real queries you can use…
Post: MySQL Slow query log in the table
May 31, 2007 By Leave a Comment
… indexes as you require. Doing so however may affect your performance – updating table with a lot of indexes may be significant overhead… MyISAM, disable concurrent inserts and run long reporting query on the log file query execution is still proceeds. I have not checked… aggregate log entirely in SQL finding most frequent slow query types or queries which load server the most. The other handly feature…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…. This will give us some interesting information about how Shard-Query will perform on each database. To do the size comparison, I… In general, a column store performs about 8x-10x better than a row store for queries which access a significant amount of… innodb-read-io-threads=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-rollback-on-timeout innodb…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… hidden corruption may later cause crashes wrong query results and further data corruption. Partial updates. MyISAM is does not have transactions this… block all updates to the table. Even worse with bulk updates queries. You do not have to have a lot of such queries to… LOCK TABLES/UNLOCK TABLES) be careful. This option may improve performance dramatically in certain cases by avoiding flushing dirty index blocks…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…. Fast Invalidation Right now when you update the table all queries have to be invalidated from the query cache… which can take a… about Query Cache content and operation can be presented as some INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache causes not uniform performance…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… looking for a way to improve update performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though… INSERT ON DUPLICATE KEY UPDATE) As I went from 1 value to 3 or 10 in the batch performance improved, especially for…_level select_lock_type != LOCK_NONE && trx->mysql_query_str) { /* Scan the MySQL query string; check if SELECT is the first word…
