…delete+Com_update+Com_replace)/Qcache_hits . These are some numbers you can play with but it …cache offering about double performance for simple queries with select done by primary key, obviously there is …party application – You can’t change how it works with MySQL to add caching but you can enable …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…: One developer on our team wants to replace longish (25-30) indexed varchars with an additional bigint column containing the crc64, and… SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL… also would note there are some MySQL optimizer restrictions in how well it can deal with primary key appended to the index…
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 to …performance. The problems are even worse with long running queries like INSERT INTO table1 … SELECT … FROM table2. In version …query like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead of a …
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…=gcomm://10.116.39.76 # replace this with the IP of your first node …33-92) requested state transfer from ‘*any*’. Selected 1 (ip-10-112-39-98)(SYNCED…with data for the benchmark. # sysbench –test=oltp –db-driver=mysql –mysql-engine-trx=yes –mysql-table-engine=innodb –mysql-host=127.0.0.1 –mysql…
Post: Using Sphinx as MySQL data retrieval accelerator
October 30, 2006 By 25 Comments
… claiming it is way faster than getting it with MySQL. Honestly I can well believe it for cases… retrieve by filters only with no full text query. Sphinx should not be replacement for Database Server but…, fast count(*) and bitmap indexes to help non selective clauses. Also some form of fast sort like…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
… here is how MySQL handles timezone locality with timestamp fields: Values… | 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…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… part is used. Let us now replace the range with IN list in this query: mysql> explain select sum(events) from info where d… original query form with single group_id query was taking 0.95 sec. The query with BETWEEN range replaced with IN list was instant 0.00 sec same as the query using join with day list table…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… try to use the MERGE algorithm with an aggregation query: mysql> create algorithm=MERGE view v2 as select c1, count(*) from v1 group… params table. Place parameters into the params table using the REPLACE statement: mysql> replace into params (`view_name`, `param1_val`, `connection_id`) values…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL storage engines. This set…, omitting the dimension columns from the projection, instead replacing them with the dimension keys: select dim_date.date_id, origin.airport_id as…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…. It also takes care of atomically* replacing the new contents of the view with the old contents (using RENAME TABLE) …than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(…
