June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

… a more complete performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I…In previous versions of MySQL, you can create two indexes with the same columns (in the same …: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows …

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… query optimizer analyzes queries for common patterns the MySQL designers know can be improved.  But there’s a finite amount of engineer… solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of randomness.  The ORDER BY RAND() solution… known to have poor performance, but it returns a better random choice. Another workaround may be to add a column to the table…

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

Post: Shard-Query adds parallelism to queries

November 15, 2010 By 18 Comments

… Year column. I partitioned the table into months using the FlightDate column using the improved MySQL 5.5 partitioning options which work directly on columns…, I could add more CPU cores. Regardless, even with a single server Shard-Query will perform much better than regular MySQL as the…

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

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

February 26, 2013 By 1 Comment

…mysql> show warnings; +———+——+————————————————–+ | Level | Code | Message | +———+——+————————————————–+ | Warning | 124 | InnoDB rebuilding table to add column…

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

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

April 4, 2011 By 20 Comments

…INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_id’,'customer_… this column. It is used to prevent wide innodb primary keys on the MV. mysql> select… use of NOW(), it but still provides improved performance during refresh by accessing summarized data. I…

Filed Under: Insight for DBAs, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… method is used: mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows affected (1 min 1.97 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql… in the buffer pool, fast index creation provides even better performance as compared to the default method, because it does not…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns…performance improvement when the workload is in-memory, in fact MRR adds…

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

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

May 6, 2011 By 23 Comments

… of the data, performance can be improved significantly: $ cat shards.ini [default] user=mysql db=ontime password= port=5029 column=date_id mapper=hash… c LIMIT 10; The next queries show how performance is improved when Shard-Query adds parallelism when “subqueries in the from clause” are…

Filed Under: MySQL

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…the performance of our database: They make the optimizer phase slower because MySQL needs…column and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql…redundant keys can help us to improve the execution time and decrease the…

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

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

…rand())); and added key on column C: alter table sbtest add key c(c); The …in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row…performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement…

Filed Under: Insight for DBAs, MySQL
« Older Posts