Archive for the ‘Cool MySQL Queries’ Category

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.

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 [...]

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:

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)?

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 [...]

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?

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.

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. 

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?  

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.