June 19, 2013

Post: More on MySQL transaction descriptors optimization

April 26, 2013 By Leave a Comment

…two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL… used a much larger one, 16 tables with 5M rows each (about 23GB). So…limitations of read-only transactions become obvious. In the following test sysbench does 9 primary key SELECTs…

Filed Under: Benchmarks, MySQL Tagged With: ,

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

…one transaction (two rows): mysql> insert into test.demo values (NULL,2),(NULL,3); Query OK, 2 rows…mysql> select * from flexviews.test_demo\G *************************** 1. row *************************** dml_type: 1 uow_id: 10 fv$server_id: 999 c1: 2 c2: 2 *************************** 2. row…

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

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…one command: mysql> create table index_analysis.used_indexes select…two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select…mysql> select * from all_indexes limit 1 \G *************************** 1. row …

Filed Under: Insight for DBAs, MySQL, Percona Software

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

April 4, 2011 By 20 Comments

…mysql> select sum(total_lines) from complete_example2 ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in set (0.68 sec) Conclusion Flexviews supports two…

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

… that the one of two things… MySQL 5.5, on the SEO data set: mysql: SELECT…| Donald Simmons | 0 | +———+——————+——————-+ There’s only one row in the… 3 DESC LIMIT 5; +——–+——————————+——————–+ | id | full_name | score | …

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

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

February 26, 2013 By 1 Comment

…one FULLTEXT index creation at a time That’s an odd limitation. Do it as two separate… 1 row in set (4.60 sec) mysql> select * from information_schema.innodb_ft_config; +—————————+———+ | KEY | VALUE | +—————————+———+ | optimize_checkpoint_limit | 180 …

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

Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

August 28, 2007 By 57 Comments

… run two separate queries: one – to get a result set, another – to count total number of rows. …: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT…use two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5…

Filed Under: Benchmarks, Insight for Developers Tagged With:

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

December 14, 2012 By 6 Comments

…and then do a set-theoretic union of the two result sets. Replace that “OR” with an “AND” and… it appears so benign. What gives? One word: selectivity. Out of 4.5M rows in the table, 4M of them had…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type)…

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

Post: UDF -vs- MySQL Stored Function

September 6, 2010 By 8 Comments

…text”, and (2) same two varchar columns needed some double characters changed to single one so “Picasso” becomes …rows: mysql> select ltrim_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql(author), remove_dups_mysql…

Filed Under: MySQL Tagged With: , ,
« Older Posts