June 20, 2013

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

April 4, 2011 By 20 Comments

…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select … for Flexviews. The flexviews.refresh() stored procedure The first parameter to flexviews.refresh() is the materialized view id…

Filed Under: Insight for DBAs, MySQL

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

… part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT name FROM… give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people…

Filed Under: Insight for Developers Tagged With: ,

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

February 26, 2013 By 1 Comment

… another, an impractical solution. So, when FTS for InnoDB was first announced, I thought this might end up being the magic… 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: , , , , ,

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

March 4, 2013 By 7 Comments

…’s default ft_min_word_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title, body… | +——+———————————————————————–+——————–+ The same query, run against InnoDB on 5.6.10: mysql: SELECT id, title, MATCH(title, body) AGAINST (‘arizona business records’ IN…

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

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

… set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1…) mysql> select sum(`count(*)`) from ex2; +—————–+ | sum(`count(*)`) | +—————–+ | 73027220 | +—————–+ 1 row in set (0.00 sec) This is very useful compression. First, the…

Filed Under: MySQL

Post: Getting MySQL to use full key length

January 31, 2007 By 5 Comments

… removing redundant indexes Here is example from NNSEEK database: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 WHERE group_id=398157…_id`,`published`) As you can see MySQL selects to use “ref” access only using two first key parts from the index and it… see MySQL selecting (A) index instead of (A,B) or even worse it can can select something like (A,D) using only first keypart…

Filed Under: Insight for DBAs Tagged With: ,

Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

July 25, 2012 By 6 Comments

…mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name | +—-+—————+——–+ | 2 | percona1 | lefred | | 3 | percona2 | kenny | +—-+—————+——–+ 2 rows in set (0.00 sec) percona2 mysql> select… first insert will take longer: percona1 mysql…

Filed Under: MySQL, XtraDB Cluster

Post: A micro-benchmark of stored routines in MySQL

August 16, 2009 By 7 Comments

…against the first query, and then executing the subquery for each row: mysql> explain select ….\G *************************** 1. row *************************** id: 1 select_type:…= c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci…

Filed Under: Benchmarks Tagged With: , , ,

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… headers… that means the first byte of byte of a file is the first byte of the first row… So we should be… then, back in mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_concat…/lib/mysql/test# myisamchk -r test_concat – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 6 – Fixing index 1 mysql> select * from…

Filed Under: MySQL

Post: MySQL: Followup on UNION for query optimization, Query profiling

August 14, 2006 By 14 Comments

…execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name…delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 42250 | | …

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