Published on : 03.05.2010 Category : Php/mysql tips Viewed : 42 times.
Code:
1.
SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword')
(Fastest)
2.
SELECT * FROM table WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)
(Fast)
3.
SELECT * FROM table WHERE RLIKE '(^| +)Keyword($| +)'
OR
SELECT * FROM table WHERE RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'
(Slow)
Contains searching
1.
Code:
SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)
(Fastest)
2.
Code:
SELECT * FROM table WHERE field LIKE 'Keyword%'
(Fast)
3.
Code:
SELECT * FROM table WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)
(Slow)
4. [/code] SELECT * FROM table WHERE field LIKE '%Keyword%'
(Slow) [/code] Recordsets
1.
Code:
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE Condition LIMIT 0, 10 SELECT FOUND_ROWS()
(Fastest)
2.
Code:
SELECT * FROM table WHERE Condition LIMIT 0, 10 SELECT COUNT(PrimaryKey) FROM table WHERE Condition
(Fast)
3.
Code:
$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.
Code:
SELECT * FROM products INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID
Returns all products with a matching supplier.
Code:
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.
RSS 2.0 :