網站製作學習誌

記錄學習製作網站的一切

[SQL] PHP MySQL Tips

在 Spanner 看到一篇 MySQL 語法的效能比較，記下來先：

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.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> MATCH <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">`field`</span><span style="color: rgb(102, 204, 102);">)</span> AGAINST <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'Keyword'</span><span style="color: rgb(102, 204, 102);">)</span>

(Fastest)

2.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> MATCH <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">`field`</span><span style="color: rgb(102, 204, 102);">)</span> AGAINST <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'+Keyword'</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">IN</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">BOOLEAN</span> MODE<span style="color: rgb(102, 204, 102);">)</span>

(Fast)

3.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">RLIKE</span> <span style="color: rgb(255, 0, 0);">'(^| +)Keyword($| +)'</span>

OR

1
2

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span>
<span style="color: rgb(153, 51, 51); font-weight: bold;">RLIKE</span> <span style="color: rgb(255, 0, 0);">'([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'</span>

(Slow)

Contains searching

1.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> MATCH <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">`field`</span><span style="color: rgb(102, 204, 102);">)</span> AGAINST <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'Keyword*'</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">IN</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">BOOLEAN</span> MODE<span style="color: rgb(102, 204, 102);">)</span>

(Fastest)

2.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">FIELD</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">LIKE</span> <span style="color: rgb(255, 0, 0);">'Keyword%'</span>

(Fast)

3.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> MATCH <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">`field`</span><span style="color: rgb(102, 204, 102);">)</span> AGAINST <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'*Keyword*'</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">IN</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">BOOLEAN</span> MODE<span style="color: rgb(102, 204, 102);">)</span>

(Slow)

4.

1

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">FIELD</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">LIKE</span> <span style="color: rgb(255, 0, 0);">'%Keyword%'</span>

(Slow)

Recordsets

1.

1
2

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> SQL_CALC_FOUND_ROWS * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> Condition <span style="color: rgb(153, 51, 51); font-weight: bold;">LIMIT</span> <span style="color: rgb(204, 102, 204);">0</span>, <span style="color: rgb(204, 102, 204);">10</span>
<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> FOUND_ROWS<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(102, 204, 102);">)</span>

(Fastest)

2.

1
2

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> Condition <span style="color: rgb(153, 51, 51); font-weight: bold;">LIMIT</span> <span style="color: rgb(204, 102, 204);">0</span>, <span style="color: rgb(204, 102, 204);">10</span>
<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> COUNT<span style="color: rgb(102, 204, 102);">(</span>PrimaryKey<span style="color: rgb(102, 204, 102);">)</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> Condition

(Fast)

3.

1
2

<span style="color: rgb(0, 0, 255);">$result</span> = <span style="color: rgb(0, 0, 102);">mysql_query</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">"SELECT * FROM table"</span>, <span style="color: rgb(0, 0, 255);">$link</span><span style="color: rgb(102, 204, 102);">)</span>;
<span style="color: rgb(0, 0, 255);">$num_rows</span> = <span style="color: rgb(0, 0, 102);">mysql_num_rows</span><span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(0, 0, 255);">$result</span><span style="color: rgb(102, 204, 102);">)</span>;

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

1
2

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> products
<span style="color: rgb(153, 51, 51); font-weight: bold;">INNER</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">JOIN</span> suppliers <span style="color: rgb(153, 51, 51); font-weight: bold;">ON</span> suppliers.SupplierID = products.SupplierID

Returns all products with a matching supplier.

1
2
3

<span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> * <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> products
<span style="color: rgb(153, 51, 51); font-weight: bold;">LEFT</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">JOIN</span> suppliers <span style="color: rgb(153, 51, 51); font-weight: bold;">ON</span> suppliers.SupplierID = products.SupplierID
<span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> suppliers.SupplierID <span style="color: rgb(153, 51, 51); font-weight: bold;">IS</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">NULL</span>

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.

Comments