June 19, 2013

Post: Innodb performance gotcha w Larger queries.

August 28, 2009 By 7 Comments

… performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem…->isolation_level select_lock_type != LOCK_NONE && trx->mysql_thd != NULL && thd_is_select(trx->mysql_thd)) { /* It is a plain locking SELECT and the…

Filed Under: Insight for Developers Tagged With:

Post: Timezone and pt-table-checksum

September 25, 2012 By 1 Comment

… the manual, here is how MySQL handles timezone locality with …crc | master_crc | +———-+————+ | 763d97f1 | 763d97f1 | +———-+————+ Node2 > EDT > select this_crc, master_crc from percona.checksums; … –sync-to-master h=node2 REPLACE INTO `foo`.`bar`(`id`, `data`, `date_…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…dimension columns from the projection, instead replacing them with the dimension keys: select dim_date.date_id, origin.airport_id… there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row …

Filed Under: MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…does during its operation. It also takes care of atomically* replacing the new contents of the view with the old …than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total…

Filed Under: Insight for DBAs, MySQL

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

March 4, 2013 By 7 Comments

… ft_min_word_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title, body) AGAINST… | +——+———————————————————————–+——————–+ The same query, run against InnoDB on 5.6.10: mysql: SELECT id, title, MATCH(title, body) AGAINST (‘arizona business records’ IN… hoping to use InnoDB FTS as a simple drop-in replacement for your current MyISAM FTS, the results may surprise you…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… do a set-theoretic union of the two result sets. Replace that “OR” with an “AND” and you might see the…’s fairly straightforward to tell when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index_merge…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE…

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

Post: MySQL Query Cache

July 27, 2006 By 70 Comments

…called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number …cache efficiency. First looking at number of your selects – Com_select and see how many of them are …by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits . These are some numbers you can…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1

October 15, 2009 By 16 Comments

… this application I can then compare using MySQL to using MySQL + Memcached, and then to other solutions like… key B.) Update data from that row and replace the stored contents on disk C.) Use the… allocated to memcached to be successful. This application selects a random # between 1 and 2 Million and…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL Tagged With: , ,

Post: Fighting MySQL Replication Lag

September 22, 2008 By 7 Comments

… there is considerable effort needed to select rows for modification – spread it out and have separate select and update queries. In such… in MySQL 5.0 (even if there are no spam posts) which will load slave significantly. You can replace it with: SELECT id… if application allows it. In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but…

Filed Under: Insight for DBAs Tagged With: ,

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

February 26, 2013 By 1 Comment

… table for the SEO dataset looks identical, except that we replace “full_name” with a VARCHAR(255) “title” and “details” with… the following statements: mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, ‘john smith’); mysql> select * from dir_test… what happens: mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from…

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