RSS

Some techniques to optimize the mysql query

08 Jun

I would like to introduce you to some very simple techniques that can speed up your queries.

1) LIMIT:


Using the limit statement is the first and most critical query statement, because the dbms ( database management system ) will stop searching when it will found the necessery records. It reduces query time specially in tables with lots of records.

examples :

SELECT * FROM table WHERE field = 'value' LIMIT 10
                    UPDATE LOW_PRIORITY table SET field = 'value' WHERE field = 'value' LIMIT 1

 

2) UPDATE LOW_PRIORITY


When you are making an update in tables that their data are not about to be used immediately, you can use update statements with LOW_PRIORITY. This way the query will be stored in a buffer, and it will be executed when the server is not busy. This type of query is perfect for statistics, session control and rate it types of tables.

example :

UPDATE LOW_PRIORITY table SET field = 'value' WHERE field = 'value' LIMIT 1

 

3) Allways search indexed fields


When you are making SELECT statements try to insert indexed fields in the WHERE clause. To create an indexed field use this command:

ALTER TABLE `table` ADD INDEX ( `field` )

 

4) INSERT DELAYED statement


When you insert a record into a table, but you do not actually want it to be available in the exact moment, and you are not about to use the insert id of the record, you can use this statement. This way, the query will be puted to a buffer, and when the database is not busy, it will execute it. This way the server is not producing overhead, and the client get the requests faster.

example :

INSERT DELAYED INTO log_table VALUES ('1', '2', '3' )
Advertisements
 
Leave a comment

Posted by on June 8, 2011 in Mixed, PHP

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: