Thalasar Ventures

MySQL has a lot of usefull features that are not known by many programmers and DBAs. This article gives a short list of such features.

SQL_CALC_FOUND_ROWS

When it is necessary to extract data limited by certain number of records at one time and get total number of records meeting the condition, keep in mind the following elegant solution:

SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

First query return 10 records meeting the condition. Second query returns number of records that should return previous query composed without LIMIT.

INSERT IGNORE

When you insert new record into a table having primary or unique key and want to avoid duplicate key error, take a look on INSERT IGNORE statement. Usually you can resolve duplicate key conflicts on PHP as follows:

 // find a row
 $ row = query(‘SELECT * FROM table WHERE id=1’);
 // if there is no row, insert new one
 if (!$ row) {
 query(‘INSERT INTO table …’);
 }

But we can do the same with just 1 MySQL query without PHP:

INSERT IGNORE INTO table …

ON DUPLICATE KEY UPDATE

There is common task – if the object exist update it, otherwise create new one. This is how we can do it on PHP:

 // look for record by certain condition
 $ row = query(‘SELECT * FROM `table` WHERE id=1’);
 // if record exists
 if ($ row) {
 // update it
 query(‘UPDATE `table` SET value=value+1 WHERE id=1’);
 }
 else {
 // insert new record
 query(‘INSERT INTO `table` SET value=1, id=1’);
 }

We can do the same with just 1 MySQL query without PHP assuming table has primary or unique key on ID:

INSERT INTO `table` SET value=1, id=1 ON DUPLICATE KEY UPDATE value=value+1

ALTER IGNORE TABLE 

Assume there is table with the following structure and data:

TestTab (f1 int, f2 int, f3 varchar(20))

    f1   f2   f3  
—————————
  1 2 row 1
  1 2 row 2
  1 3 row 3
  2 3 row 4
  3 4 row 5 

Now it is required to remove duplicate entries in first two columns from the table.
We can do this by running the following MySQL query:

ALTER IGNORE TABLE `TestTab` ADD UNIQUE INDEX(f1,f2);

More articles about MySQL and other databases can be found at
http://www.convert-in.com/articles.htm