By JortK on February 9, 2010
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 [...]
Posted in MySQL | Tagged benchmark, cast, error, join, monitor, MySQL, query, tutorial
By JortK on November 28, 2009
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 [...]
Posted in MySQL | Tagged best practices, cache, database, db, MySQL, query, select, show
By JortK on June 30, 2009
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 <= [...]
Posted in How to | Tagged database, limit, oracle, query, top, trick
By JortK on June 27, 2009
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 [...]
Posted in How to | Tagged limit, ms-sql, mssql, query, sql, top, trcik
By JortK on June 26, 2009
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` [...]
Posted in How to | Tagged database, db, limit, MySQL, query, rownum, top, trick
By JortK on July 23, 2008
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 [...]
Posted in MySQL | Tagged exists, in, MySQL, performance, query