Database Reference
In-Depth Information
+----------+
| COUNT(*) |
+----------+
| 69 |
+----------+
mysql>
SELECT COUNT(*) from kjv
->
WHERE MATCH(vtext) AGAINST('Abraham')
->
AND bname = 'Hebrews';
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
mysql>
SELECT COUNT(*) from kjv
->
WHERE MATCH(vtext) AGAINST('Abraham')
->
AND bname = 'Hebrews' AND cnum = 11;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
If you expect to use search criteria frequently that include other non-
FULLTEXT
columns,
add regular indexes to those columns so that queries perform better. For example, to
index the topic, chapter, and verse columns, do this:
mysql>
ALTER TABLE kjv ADD INDEX (bnum), ADD INDEX (cnum), ADD INDEX (vnum);
Search strings in full-text queries can include more than one word, and you might
suppose that adding words would make a search more specific. But in fact that widens
it because a full-text search returns rows that contain any of the words. In effect, the
query performs an
OR
search for any of the words. The following queries illustrate this;
they identify successively larger numbers of verses as additional search words are added:
mysql>
SELECT COUNT(*) from kjv
->
WHERE MATCH(vtext) AGAINST('Abraham');
+----------+
| COUNT(*) |
+----------+
| 229 |
+----------+
mysql>
SELECT COUNT(*) from kjv
->
WHERE MATCH(vtext) AGAINST('Abraham Sarah');
+----------+
| COUNT(*) |
+----------+
| 243 |
+----------+
mysql>
SELECT COUNT(*) from kjv
->
WHERE MATCH(vtext) AGAINST('Abraham Sarah Ishmael Isaac');
+----------+
| COUNT(*) |
