… in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 7, 2012 By 12 Comments
…:”Then MySQL will use the key as if its only defined as including …shipinstruct. And so all rows matching condition l_partkey = x and and l_quantity…MySQL server which will then in turn apply the remaining parts of the WHERE clause l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause…if you use AND between such columns in where clause. Index merge does helps performance but it is far from performance of combined index in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY clauses…in the data dictionary: mysql> select * from flexviews.mview_table where…condition: USING (order_id) mview_join_order: 999 3 rows in…_customer_sales table. If you were paying …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…in the data set. mysql> select count(*) from dim_airport; +———-+ | count(*) | +———-+ | 396 | +———-+ 1 row in… conditions are placed on attributes in…in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If…using (flight_id) WHERE Year …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…if you’re just using ORDER BY without and where clauses on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very important to understand in which conditions…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY … adjusted in the future. The main thing to watch for, if you do not have full where clause …as it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different …
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… to handle the output of each input asynchronously, if you like. Right now I believe this only … “buckets” in the table. This allows MySQL to set up a sequential scan over the items in this bucket, … BETWEEN 1 and 6 added to the where clause. This creates boundary conditions for our query. Any set of …
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…) If we then write a SQL query to aggregate over the rows of the view and we add a filter condition on… row in set (1.66 sec) Even a query with an impossible where clause takes a long time to process: mysql> select * from v2 where c1 = 100; Empty set (1.64 sec) You can see that MySQL…
Post: Derived Tables and Views Performance
August 31, 2006 By 14 Comments
…to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running …| +—+———————————-+ 1 row in set (0.10 sec) Here are couple of explains if you are curios mysql> explain select * from v where i=…
