Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10

Displaying posts with tag: Cool MySQL Queries (reset)

MySQL Left Join
+7 Vote Up -0Vote Down
posted by Ilan Hazan on Wed 06 Jul 2011 09:23 UTC
Tags:
(edit) on, left join, Cool MySQL Queries, is null
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. A [...]
As restoring a dump table into the MySQL master – you better get some sleep
+6 Vote Up -0Vote Down
posted by Ilan Hazan on Mon 04 Apr 2011 08:09 UTC
Tags:
(edit) sleep, mysqldump, Cool MySQL Queries, MySQL Script
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
+4 Vote Up -0Vote Down
posted by Ilan Hazan on Mon 14 Mar 2011 19:02 UTC
Tags:
(edit) Cool MySQL Queries, MySQL feature
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: The insertion will be faster: because the primary key cannot disabled, every insertion will also updates the primary key. In a case [...]
A many to many relationship table – solving the exclude relation problem
+8 Vote Up -0Vote Down
posted by Ilan Hazan on Tue 18 Jan 2011 12:36 UTC
Tags:
(edit) Cool MySQL Queries, pivot, exclude, inner query, many to many, relation
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)? I will start by defining the MySQL Many to Many relationship (Experts can skip to the next paragraph) What is A MySQL many [...]
The battle between FORCE INDEX and the QUERY OPTIMIZER
+4 Vote Up -0Vote Down
posted by Ilan Hazan on Mon 25 Oct 2010 21:15 UTC
Tags:
(edit) Cool MySQL Queries, MySQL feature
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
+4 Vote Up -1Vote Down
posted by Ilan Hazan on Thu 07 Oct 2010 12:06 UTC
Tags:
(edit) performance, query, Cool MySQL Queries, Comparison operator, IN, MySQL IN, union all
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(*)
+3 Vote Up -0Vote Down
posted by Ilan Hazan on Thu 13 May 2010 21:52 UTC
Tags:
(edit) select, performance, limit, Cool MySQL Queries, count(*), limited 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. The [...]
Conditional Joins in MySQL
+3 Vote Up -0Vote Down
posted by Ilan Hazan on Sun 07 Feb 2010 19:43 UTC
Tags:
(edit) Cool MySQL Queries
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.  Here’s an example: Suppose you have three tables: questions: a table consisting of question ids, timestamps, and whether [...]
Selecting records from one table that dont have specific relationship in a second table
+3 Vote Up -0Vote Down
posted by Ilan Hazan on Sun 17 Jan 2010 19:57 UTC
Tags:
(edit) Cool MySQL Queries
Question: How can you select records from one table that doesn’t have a specific ID in a second, many-to-many table?   In other words, imagine you have a questions table (A), categories table (B), and a many-to-many relationship link table (L).  This setup allows a question to have several categories.  How can you find questions that [...]
How to produce random rows from a table
+1 Vote Up -0Vote Down
posted by Ilan Hazan on Sun 29 Nov 2009 04:10 UTC
Tags:
(edit) Cool MySQL Queries
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. For example by producing random numbers in PHP and using them in the MySQL query in a IN clouse.  However, I [...]
Showing entries 1 to 10

Planet MySQL © 1995, 2013, 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.