query
11 MySQL resources you must read!
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
Monitoring your MySQL errors
Great tutorial on how to monitor your MySQL Server for errors.
New OLAP Wikistat [...]
The 20 best practices when using MySQL
Just came across this great article with 20 best practices when using MySQL: MySQL best practices.
Be sure to check them, the following items are in the article:
Optimize Your Queries For the Query Cache
LIMIT 1 When Getting a Unique Row
Get Suggestions with PROCEDURE ANALYSE()
Prepared Statements
Split the Big DELETE or INSERT Queries
Be Careful with Persistent Connections
And more [...]
Using TOP in Oracle queries
We already discussed the methods to use TOP functions in MySQL and in MSSQL.
In Oracle we make use of the ROWNUM function in the WHERE clause.
To take our names table again in this example, to get the first 5 rows, make use of the following query:
SELECT firstname,lastname
FROM names
WHERE ROWNUM <= 5
This will retrieve the first [...]
Using TOP in MS-SQL queries
After I explained the MySQL method for getting the TOP N rows in a query it is time to explain the same for MS-SQL.
Here we use the TOP function.
To retrieve the first 5 rows from the names table, use the following query:
SELECT TOP 5 firstname,lastname
FROM names
If you want to retrieve a certain percentage (for example [...]
Using TOP in MySQL queries
On the web I see a lot of questions of developers who are wanting to know how they can retrieve only the first N records in their queries.
The answer is simple, LIMIT
For example, if you only want to select the top 5 names from a names tables, use the following query:
SELECT `firstname`,`lastname`
FROM `names`
ORDER [...]
EXISTS much faster then IN in MySQL
Today I was making some queries on a database with a lot of records.
The table I was making the query on had 25.000.000 records.
The performance of the query I’ve made was terrible, the structure was as follows (fake column names used):
SELECT * FROM t_posts
WHERE id IN
(SELECT id
FROM t_topics
WHERE id BETWEEN 150000 AND 150001)
This query took [...]