MySQL Left Join

Even if you think you know everything about LEFT JOIN, I bet you will learn something or two in this post!

  • The difference between the ON clause and the WHERE clause.
  • A simple way to better understand a complex Matching-Condition with WHERE … IS NULL clause.
  • The difference between the Matching-Conditions and the Where-conditions.

Read the rest of this entry »

As restoring a dump table into the MySQL master – you better get some sleep

Restoring a dump table into the MySQL master server can lead to serious replication delay. The massive inserts commands cause the Master and slaves to use most of their resources for replication. As a result, replication lag may increase dramatically (linear to the table size).
To overcome the replication delay, caused by restoring the dump table on the master, there is a need to widespread the massive inserts. This can be done by the MySQL SLEEP command: Read the rest of this entry »

If you copy a MyISAM table with PRIMARY key, don’t forget to order the rows first

In a case that you copy (INSERT INTO .. SELECT ..) a MyISAM table that have a PRIMARY key, it is much faster to insert the new rows in the primary key order: Read the rest of this entry »

You Must Have A Killer In Your System

Every system can have a slow query that randomly seems to emerge from nowhere. The main threat from these slow queries is that it can unexpectedly bring down your site. Even though the query is not called often (less than one percent), it can definitely harm your site performance. That is why you should have a Killer in your system. Read the rest of this entry »

A many to many relationship table – solving the exclude relation problem

A MySQL many to many relationship is a relationship that is multi-valued in both directions. Given that, how can you select rows that don’t have a specific relationship (the exclude relation problem)?
Read the rest of this entry »

The battle between FORCE INDEX and the QUERY OPTIMIZER

Query Optimizer is a part of the server that takes a parsed SQL query and produces a query execution plan.

MySQL Query Optimizer uses (as one of its parameters) the stored key distribution (Cardinality) mechanism to determine the order in which tables should be joined, when you perform a join on something other than a constant and also for determining which indexes to use for a specific table within a query.

However, BEWARE! Read the rest of this entry »

Optimizing the MySQL IN() Comparison Operations Which Include the Indexed Field

The MySQL IN() Comparison Operator is said to be very quick if all the values are constants (the values are then evaluated and sorted first, and the search is done using a binary search).
However, what if the field which the IN clause refers to, is part of the index used to execute the query?
Read the rest of this entry »

MySQL User Defined Variables

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is much more to explore.
Read the rest of this entry »

Limited SELECT count(*)

A SELECT statement with COUNT returns the number of rows retrieved by the SELECT statement (see mysql select count).

For performance reasons, the desired result is to limit that count.
Including a LIMIT clause in the SELECT statement will not work since it only restricts the number of rows returned, which is always one.
Read the rest of this entry »

Conditional Joins in MySQL

One way to do a “Conditional Join” in MySQL is by using a “LEFT JOIN”.  Create a “LEFT JOIN” for each condition and combine the results into one column using an “IF” statement by the “SELECT” expression.  Read the rest of this entry »

Selecting records from one table that dont have specific relationship in a second table

Question: How can you select records from one table that doesn’t have a specific ID in a second, many-to-many table?  
Read the rest of this entry »

The fading of Query Cache limitations

There is no question about the MySQL Query Cache greatness. 

The MySQL Query Cache feature started his way with some limitations.  However, in time, there were a lot of improvements (fixing) for those limitations.
Read the rest of this entry »

How to retain comments in MySQL statements

There is a lot of buzz lately around retaining comments in MySQL’s logs and MySQL Stored procedures (Routines).
When analyzing a MySQL query, it’s nice to have a comment inside the query (or procedure) that can provide us with more info about the query. Read the rest of this entry »

How to produce random rows from a table

A while ago, I were searching for a way to produce random rows from a table in MySQL. I found several solutions but none of them satisfied me. Of course, I could use  a combined logic of MySQL and a programming language. Read the rest of this entry »