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

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword')

(Fastest)

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)

(Fast)

SELECT * FROM TABLE WHERE RLIKE '(^| +)Keyword($| +)'

OR

SELECT * FROM TABLE WHERE
RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'

(Slow)

Contains searching

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)

(Fastest)

SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'

(Fast)

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)

(Slow)

SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'

(Slow)

Recordsets

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT , 10

SELECT FOUND_ROWS()

(Fastest)

SELECT * FROM TABLE WHERE Condition LIMIT , 10

SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition

(Fast)

$result = mysql_query("SELECT * FROM table", $link);
$num_rows = mysql_num_rows($result);

(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.

SELECT * FROM products
INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID

Returns all products with a matching supplier.

SELECT * FROM products
LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID

WHERE suppliers.SupplierID IS NULL

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.