June 19, 2013

Post: More on MySQL transaction descriptors optimization

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

Post: Using Flexviews - part two, change data capture

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

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

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

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

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

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

… 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 | …

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

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 …

Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

… 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…

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

…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)…

Post: UDF -vs- MySQL Stored Function

…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