mySQL: Select statements

mysqlsearch.mysql

Word searching
 
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.
 
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)
 
(Fastest)
 
2.
 
SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'
 
(Fast)
 
3.
 
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)
 
(Slow)
 
4.
 
SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'
 
(Slow)
Recordsets
 
1.
 
SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT FOUND_ROWS()
 
(Fastest)
 
2.
 
SELECT * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition
 
(Fast)
 
3.
 
$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.

gistfile1

Word searching
 
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.
 
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)
 
(Fastest)
 
2.
 
SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'
 
(Fast)
 
3.
 
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)
 
(Slow)
 
4.
 
SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'
 
(Slow)
Recordsets
 
1.
 
SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT FOUND_ROWS()
 
(Fastest)
 
2.
 
SELECT * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition
 
(Fast)
 
3.
 
$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.