… mview_table_schema: demo mview_table_alias: c mview_join_condition: USING (customer_id) mview_join_order: 999 *************************** 3. row *************************** mview_table…: ol mview_join_condition: USING (order_id) mview_join_order: 999 3 rows in set (0.00 sec) Using the dictionary The following… cases where this is not useful, simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… unless opening and closing files is expensive operation (such as using MySQL on NFS file system). innodb_data_file_path got a…. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select * from var55 where variable…_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from var56 where variable_name…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 30 Comments
… conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID… join syntax is more expressive and you’re going to use it anyway for LEFT JOIN, and mixing styles is asking…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
…new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and …scans, where index parts after the part on which range condition is applied cannot be used for…l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1… level. Condition pushdown is one of the new features of MySQL 5.6, and actually is a great improvement over MySQL 5.5…_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in set (0.00 sec) I reported…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… Using index condition; Using where; Using MRR | +—-+————-+———-+——-+—————+—————+———+——+———+———————————————–+ See there are new Extra notes USING INDEX condition; USING… of query, as MySQL used index to scan …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… server does everything itself. Especially when using a CMS where DB structure is prepdefined A: MySQL Server will not automatically define any indexes… search condition WHERE A=5 ORDER BY B will use index (A,B) for sorting optimization, for more complicated conditions you will need to use…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…our world can become an amazing place. Please use this technology constructively and for peaceful purposes…. as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), … and 6 added to the where clause. This creates boundary conditions for our query. Any set …
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
… t2 WHERE ID1=1; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition …t2 | ref | ID1 | ID1 | 5 | const | 81371 | Using where | +—-+————-+——-+——+—————+——+———+——-+——-+————-+ That is MySQL will use index for this query. Let’…
Post: MySQL Connection Timeouts
April 19, 2011 By 4 Comments
… for many conditions. Now lets look more into the problem and do some Math. First lets look into how MySQL accepts connection… traffic you will see some seconds where as much as 3000 connections come in. Under such conditions the default back_log of… connections a second to MySQL Server you might be pushing your luck and at very least you’re using a lot of…
