June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… when using replication, a temporary table could vanish if the slave restarts and… is that it requires storing another column, and reinitializing the sequence after making…why it helped? When I did not use the STRAIGHT_JOIN, the query… you have used a SUBQUERY to fetch and compare MAX(episode_id) as …

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

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

…a binary search on a B-tree node. We could not compare a search key K to an index … needs to access one of the pages being fetched does it has to wait for whole read …not form a primary key or a unique constraint: it is sufficient if the referenced columns form a consecutive set of leftmost columns…

Filed Under: Insight for Developers Tagged With: ,

Post: Multi-Column IN clause - Unexpected MySQL Issue

April 4, 2008 By 21 Comments

… one is however not efficient if you’re processing millions of them so we tried to do bulk fetches: mysql> explain SELECT… should not be surprised though as multi-column in is not the most used MySQL feature out there. For given application case we could simply rewrite query using more standard single column…

Filed Under: Insight for Developers Tagged With: ,

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then… for each secondary key entry fetched. This means that secondary key lookups for column values that do not belong to the secondary index… to be reinitialized, because the buffer was small and not all index tuples could fit in the buffer. If this is > 0…

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

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …

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

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… finish the query on IO bound workload so that it could fit well in the chart, in actuality the query took… which results in random scans of the PK to fetch the columns that are not part of the secondary key. Even though MySQL… table lineitem, is joined by the column l_orderkey which is the left-most PK column, so reading the table orders in…

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

Post: Shard-Query adds parallelism to queries

November 15, 2010 By 18 Comments

…single CPU when aggregating data and fetching rows from the buffer pool, with… how much improvement could be made to MySQL so that is could better utilize …natively, but most of those are not open source. In the future, …to use the FlightDate column instead of the Year column. See the “full disclosure”…

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

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

… not fit into memory or if the caches are not warm, this also does not take into account locality of access, as you could… other optimizations like for example if columns other then the secondary key columns are required to be fetched, then MRR interface can instead access the PK by arranging the secondary key values by PK column…

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

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

…thousand of matched records, checking attributes is not going to slow down performance of queries … to give slightly different results it could be because the indexing occurred between different…fetch when indexing the catalog: CONCAT(‘userkey_’, user_id) userkey And now I have an extra column…

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

Post: Implementing efficient counters with MySQL

July 1, 2007 By 16 Comments

… When you would simply fetch given object information now you do not only fetch the data but also … counter table, having say “id” and “counter” columns. Using separate table for updates already solves …If you’re in system programming you perhaps could implement cache with such semantics and it …

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