On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If …there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw… logically the same, I believe it should all translate to the same execution plan. …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size | 268435456 | +———————+———–+ If these are not the same…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… I worked on optimizing application which uses VIEWs and was looking at the long running query which just joined 2 tables… I… if you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts. MySQL has two ways…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
…on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables… same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries … as if we assume MySQL users do not join lots of tables (less than 7) when…
Post: MySQL - to use or not to use
May 28, 2007 By 10 Comments
…MySQL 4.0. This even applies to Innodb tables which were already on par with general MySQL level of stability in MySQL…Oracle and MySQL differently and if you’re try to apply same patterns to MySQL as …MySQL adding in newer MySQL releases. In other cases I can see people using for example SubQueries…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…code is at doing basically the same thing the subquery code does natively (so to speak)….as co inner join CountryLanguage as cl on cl.CountryCode = co.Code where lower(cl.Language) = ‘English’);… a table scan against the first query, and then executing the subquery for each row: mysql> explain select …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from …cores for this test. The same VM was used as the …Shard-Query adds parallelism when “subqueries in the from clause” are … in cooperation with Infobright, including on benchmarking. These particular tests were …
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… speedier joins, faster subqueries, and elimination of useless tables in joins Thread … Percona Server has the same group commit fix, and MySQL 5.6 offers microsecond…based on MySQL 5.1, but MariaDB 5.5 will be based on MySQL 5.5… “fix subqueries and you will change the world for a lot of MySQL users.”…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…multiple buffers of the same type might be allocated for exampe to handle subqueries. For some …MySQL Server Memory Requirements Thread buffers can be allocated more than once for each thread. Consider for example subqueries…table_size etc Many variabes can be set per connection. So you can’t relay on…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…view delta table and thus the logic for applying changes via ON DUPLICATE KEY UPDATE is the same. All…in this blog post. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to…resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies…
