Some tips to optimize MySQL

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` )

Well that's all for now, I will write more when I have more time....

Let me continue this small tutorial :

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' )

Here is the full documentation for INSERT DELAYED

Wow, I'll be checking back more on this thread- I didnt know like, half of it :LOL: Glad you wrote it. Thanks!!

Interesting, I didn't knew that you can do such things with mySql

Shouldn't 2) be ... when the server is not busy. ?

Hehe. You are right.

I've just corrected it.