PHP Mysql tips

Continuing from my earlier post on PHP performance, I thought I'd share a few Mysql tips that I've learnt over the years. Hope it helps someone and please leave a comment with your own tips or provide any corrections to the ones mentioned.

Word searching

1. <pre lang="sql">SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword')</pre>

(Fastest)

2.<pre lang="sql">SELECT * FROM table WHERE MATCH (field) AGAINST ('+Keyword' IN BOOLEAN MODE)</pre> (Fast)

3.<pre lang="sql">SELECT * FROM table WHERE RLIKE '(^| +)Keyword($| +)'</pre> OR

<pre lang="sql">SELECT * FROM table WHERE RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'</pre>

(Slow)

Contains searching

1. <pre lang="sql">SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)</pre>

(Fastest)

2. <pre lang="sql">SELECT * FROM table WHERE field LIKE 'Keyword%'</pre>

(Fast)

3. <pre lang="sql">SELECT * FROM table WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)</pre>

(Slow)

4. <pre lang="sql">SELECT * FROM table WHERE field LIKE '%Keyword%'</pre>

(Slow)

Recordsets

1. <pre lang="sql">

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE Condition LIMIT 0, 10 SELECT FOUND_ROWS() </pre>

(Fastest)

2.<pre lang="sql"> SELECT * FROM table WHERE Condition LIMIT 0, 10 SELECT COUNT(PrimaryKey) FROM table WHERE Condition </pre>

(Fast)

3. <pre lang="php">

$result = mysql_query("SELECT * FROM table", $link); $num_rows = mysql_num_rows($result); </pre> (Very slow)

Joins

Use an INNER JOIN when you want the joining table to only have matching records that you specify in the join. Use LEFT JOIN when it doesn't matter if the records contain matching records or not.

<pre lang="sql"> SELECT * FROM products INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID </pre>

Returns all products with a matching supplier.

<pre lang="sql"> SELECT * FROM products LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID WHERE suppliers.SupplierID IS NULL </pre>

Returns all products without a matching supplier.

Best practice

1. Always use lowercase for table names. (If you use different OS's this is a must)
2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode. This makes multiple joins very easy.
3. Always create a primary id field with the name of the table followed by the id. e.g. ProductID
4. Index fields used for joins.
5. Use a separate logging table or transactions for logs of table updates, deletes etc.