…, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from… Join As I have told before MySQL has only supported one join algorithm and that is Nested Loop …, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…address, c_comment order by revenue desc LIMIT 20; In-memory workload Now … mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ …123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1…rows_read are incremented by ONE. However, when MRR is used then there are two…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 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…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…mysql> select sum(total_lines) from complete_example2 ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in set (0.68 sec) Conclusion Flexviews supports two…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 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…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 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…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1 row in… servers). Due to ICE limitations, the data was split …two new test queries have been added. To model real world complexity, each of the test queries feature at least one…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… app I created that reads two rows, and update one of them to read from… I can then compare using MySQL to using MySQL + Memcached, and then to …. When your dataset exceeds your memory limitations or the database can simply …memcached to be successful. This application selects a random # between 1 and …
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… see MySQL selects to use “ref” access only using two first key parts from the index and it assumes there is only one row to… 23:59:59′) ORDER BY published desc LIMIT 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: msg132 type: range possible…
