Posted By

j_junyent on 01/13/08


Tagged

mysql search


Versions (?)

Who likes this?

21 people have marked this snippet as a favorite

luman
adix
axthos
DeadLy
uisluu
heinz1959
crs0328
wizard04
jadedbat
latavish
d8n
Zwendel
mchadwick
DesignByWilliam
dinkan
tets
ljayz
bobbym245
jfoy
lfcortes
ringo380


MySQL search


 / Published in: SQL
 

URL: http://www.thespanner.co.uk/2007/07/23/php-mysql-tips

http://sentidoweb.com/2007/07/25/consejos-mysql.php

  1. Word searching
  2.  
  3. 1.
  4.  
  5. SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword')
  6.  
  7. (Fastest)
  8.  
  9. 2.
  10.  
  11. SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)
  12.  
  13. (Fast)
  14.  
  15. 3.
  16.  
  17. SELECT * FROM TABLE WHERE RLIKE '(^| +)Keyword($| +)'
  18.  
  19. OR
  20.  
  21. SELECT * FROM TABLE WHERE
  22. RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'
  23.  
  24. (Slow)
  25. Contains searching
  26.  
  27. 1.
  28.  
  29. SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)
  30.  
  31. (Fastest)
  32.  
  33. 2.
  34.  
  35. SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'
  36.  
  37. (Fast)
  38.  
  39. 3.
  40.  
  41. SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)
  42.  
  43. (Slow)
  44.  
  45. 4.
  46.  
  47. SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'
  48.  
  49. (Slow)
  50. Recordsets
  51.  
  52. 1.
  53.  
  54. SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10
  55. SELECT FOUND_ROWS()
  56.  
  57. (Fastest)
  58.  
  59. 2.
  60.  
  61. SELECT * FROM TABLE WHERE Condition LIMIT 0, 10
  62. SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition
  63.  
  64. (Fast)
  65.  
  66. 3.
  67.  
  68. $result = mysql_query("SELECT * FROM table", $link);
  69. $num_rows = mysql_num_rows($result);
  70.  
  71. (Very slow)
  72. Joins
  73.  
  74. 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.
  75.  
  76. SELECT * FROM products
  77. INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID
  78.  
  79. Returns ALL products WITH a matching supplier.
  80.  
  81. SELECT * FROM products
  82. LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID
  83. WHERE suppliers.SupplierID IS NULL
  84.  
  85. Returns ALL products without a matching supplier.

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: DeadLy on January 21, 2008
  1. SELECT * FROM TABLE WHERE MATCH (field) AGAINST ('Keyword' IN BOOLEAN MODE)

it's false by default setting

You need to login to post a comment.