On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that… query where you were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to…
Post: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
… on what’s seriously limiting MySQL in certain circumstances (links: part 1, 2). This post is about subqueries, which in some cases… on subqueries in MySQL, so I’ll try to be gentle. The following query will surprise users unpleasantly: select * from a where a… also have poor subquery performance, or have had it in the past and have fixed it. And many MySQL users have learned…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select * from var55 where variable… 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from var56 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row…,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on 1 row in set (0.00 sec) Summary: MySQL 5.6…
Post: Top 5 Wishes for MySQL
June 29, 2007 By 10 Comments
… as single MySQL server with improved performance and high availability. Of course there is MySQL Cluster but it does not get where quite yet having very particular performance properties and operating requirements. In fact I do not think MySQL Cluster would… on MySQL because subqueries optimizer weakness. I’m saying about XA which were released in MySQL 5.0 without consistent work with MySQL close…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
…about the way MySQL handles connections, allocating one thread per connection to the server. MySQL is a …of application servers can lead to a situation where the database server has a very large … which are two major limitations. And it has subqueries, which are a core, fundamental part of SQL –…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…SUBQUERY *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY Now I took the subquery and basically rewrote it as a stored function. mysql> delimiter // mysql…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | …improved when Shard-Query adds parallelism when “subqueries in the from clause” are used. …id) JOIN dim_flight using (flight_id) WHERE Year BETWEEN 2009 and 2011 GROUP …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult… queries with subqueries in the FROM clause, which will actually execute the inner subquery!), and examine the query plan. This is where you…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
…. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts. MySQL has two ways… number of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places, so you can learn more about where… back I told Monty, “fix subqueries and you will change the world for a lot of MySQL users.” Well, that dream has come true, and now we have subqueries that execute inside-out…
