|
Recent
Articles |
Removing Nulls From A DataTable
The individual cells in a DataTable can have a null value in the form of System.DbNull.Value. If the DataTable is created by querying a database through ADO.NET, you can write the SQL statement in a way eliminates nulls. It could look like this "SELECT isnull(name, 'n/a')...
Compact An Access Database From .NET
Over time, a database becomes fragmented when it is used. This makes it grow in size and perform badly. Not the two qualities you want for your mission-critical data. Luckily, a lot of databases such as MS SQL Server and Oracle can compact it self, or at least have an interface...
Oracle Responds To Information Security Critics
Oracle the Database Company is taking time out on its corporate blog to address a number of critics on the security of Oracle Databases. Like any other application, the security of the application is only as good as how it was configured. It is very easy to take a default...
The What, Why, And How Of Database Cleansing
Firstly the "What" - Cleaning a database is done to: Remove duplicate records. Ensure your data is consistently formatted. Correct data that is obviously wrong e.g. wrong postcode for a known suburb. Find other records that are likely to be the same (more on...
Oracle & Red Hat's Symbiotic Relationship
There is a symbiotic relationship between Oracle & Red Hat now. Yes there was one before with Oracle's database business on Linux, but it's even more intertwined now. This relationship is similar to what we see between...
Oracle After Enterprise Web 2.0 Blood
One of the sure signs that there is money to made in a technology sector is when the big sharks start gathering around spots where the little fish have been happily churning up some waves. The announcement that uber-shark Oracle has come up with a "new" product...
Countdown To Oracle Open World
With all of the acquisitions Oracle has done over the past twelve plus months, this year's Oracle Open World Conference is certain to set new records in both attendance and presentations. I've heard rumors that San Francisco can expect around 40,000 Oracle...
|
|
|
|
01.03.07
MySQL Optimization Tips
By
Pete Freitag
Every programmer loves to optimize, even when we know we shouldn't.
To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.
I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.
It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.
Let's take a look at some MySQL Optimization Hints:
SQL_NO_CACHE
The SQL_NO_CACHE hint turns off MySQL's builtin query caching mechanism for a particular query. You can help MySQL make the query cache more efficent by using this hint on queries that are highly dynamic (such as a keyword search, or a report that only runs nightly). Make sure query caching is turned on otherwise there is no need for this command.
Checkout my article on the MySQL Query Cache for more info.
SQL_CACHE
If you have setup MySQL Query Caching to explicit mode (set query_cache_type = 2) then you can use the SQL_CACHE hint to tell MySQL which queries to cache.
My article on the MySQL query cache also covers this hint.
HIGH_PRIORITY
The HIGH_PRIORITY hint can be used on SELECT or INSERT statements to let MySQL know that this is a high priority query. This hint will basically allow the query to skip in line.
LOW_PRIORITY
The LOW_PRIORITY hint can be used on INSERT and UPDATE statements. If you use the LOW_PRIORITY keyword, execution of the query is delayed until no other clients are reading from the table. This means that you may wait a LONG time, or forever on servers with a heavy read volume.
INSERT DELAYED
An INSERT LOW_PRIORITY statment will not return until the statement has been executed, which could possibly be forever. Instead you can use an INSERT DELAYED statement. It will return immediately, but it will still wait until other clients have closed the table before executing the statement.
Continue reading this article.
About the Author:
Pete Freitag (http://www.petefreitag.com/) is a software engineer, and
web developer located in central new york. Pete specializes in the
HTTP protocol, web services, xml, java, and coldfusion. In 2003 Pete
published the ColdFusion MX Developers Cookbook with SAMs Publishing.
Pete owns a Firm called Foundeo (http://foundeo.com/) that specializes
in Web Consulting, and Products for Web Developers.
|
