Even if you think you know everything about LEFT JOIN, I bet you will learn something or two in this post!
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 »
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 »
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 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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
