Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 24 10 Older Entries

Displaying posts with tag: join (reset)

Tricking the Optimizer, or How Checking Bug Reports Help to Solve Real Problems
+0 Vote Up -0Vote Down

I've got several useful habits over the years of work in MySQL Support. One of them is to start working on every problem with search for known MySQL bugs related to the problem at hand. I'd like to share one recent case where this habit helped me to get a solution for customer almost instantly.

It was one of rare cases when customer opened a support request with a very clear question and even a test case. The problem was described very precisely, more or less as follows (with table and column names, and data changed for this blog post, surely).

Let's assume we have two tables created like these:





  [Read more...]
3 Simple Patterns for Tighter MySQL Code
+0 Vote Up -0Vote Down

Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]

The post 3 Simple Patterns for Tighter MySQL Code appeared first on Scalable Startups.

Join Optimizations in MySQL 5.6 and MariaDB 5.5
+1 Vote Up -0Vote Down

This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5

The post Join Optimizations in MySQL 5.6 and MariaDB 5.5 appeared first on ovais.tariq.

Improvements for many-table joins in MySQL 5.6
Employee +4 Vote Up -0Vote Down

A lot has happened in MySQL 5.6 for queries joining many tables. For the most common use cases we have drastically reduced the cost of finding the execution plan. We have also improved the heuristics and removed bugs so that the final plan is often better than it used to be. Read on if you are one of those people who do 15 way joins!

Finding a query execution plan
First some background. You can skip this part if you know how MySQL picks the table join order in 5.5.

When presented with a query, MySQL will try to find the best order to join tables by employing a greedy search algorithm. The outcome is what …




  [Read more...]
MySQL DELETE Join example
+0 Vote Up -0Vote Down

A very useful helper in your join toolbox can be a delete join. Even though it’s not a special join type but a join used within DELETE statements, it’s still worth mentioning. However, from time to time when I want to make use of delete joins  on my own, I somehow managed it to forgot the syntax and have to look it up somewhere. Therefor, here is a description as well as an example.

Take care: A delete join is a very powerful weapon. And with great power comes great responsibility! I recommend to develop the delete join on a development database. At least, …

  [Read more...]
SQL JOINing a Table to Itself
+0 Vote Up -0Vote Down

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, …

  [Read more...]
SQL JOINing a Table to Itself
+1 Vote Up -0Vote Down

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly …

  [Read more...]
70x Faster Joins with AQL now GA with MySQL Cluster 7.2
+11 Vote Up -0Vote Down

70x faster joins with AQL

The new GA MySQL Cluster 7.2 Release (7.2.4) just announced by Oracle includes 2 new features which when combined can improve the performance of joins by a factor of 70x (or even higher). The first enhancement is that MySQL Cluster now provides the MySQL Server with better information on the available indexes which allows the MySQL optimizer to automatically produce better query execution plans. …

  [Read more...]
SQL Joins with On or Using
+0 Vote Up -0Vote Down

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 | …
  [Read more...]
SQL Joins with On or Using
+1 Vote Up -0Vote Down

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  | …
  [Read more...]
Showing entries 1 to 10 of 24 10 Older Entries

Planet MySQL © 1995, 2016, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.