MySQL Full-Text Search
From w3cyberlearnings
Contents |
Fulltext search in Mysql
- Fulltext search all the words within the document
as it tries to match the word criterial.
- When LIKE and Regular Expression can not be fullfil
- Fulltext search is faster
- FullText search uses the MyISAM engine
Create Table
CREATE TABLE myarticle
(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
article TEXT NOT NULL,
FULLTEXT(title),
FULLTEXT(article),
FULLTEXT(title,article),
PRIMARY KEY(id)
)Engine=MyISAM;
use ALTER TABLE to add full-text search to your table if you haven't done so
ALTER TABLE myarticle ADD FULLTEXT(title, article); ALTER TABLE myarticle ADD FULLTEXT(title), ADD FULLTEXT(article);
Insert sample records
INSERT INTO myarticle(title,article)
VALUES('The world history and science','The study of mathematics as a subject in its own right
begins in the 6th century BC with the Pythagoreans,
who coined the term "mathematics" from the ancient Greek μάθημα (mathema),
meaning "subject of instruction".
Greek mathematics greatly refined the methods');
INSERT INTO myarticle(title,article)
VALUES('Islamlic Mathematics study',
'The Islamic Empire established across Persia,
the Middle East, Central Asia, North Africa,
Iberia, and in parts of India in the 8th century
made significant contributions towards mathematics.
Although most Islamic texts on mathematics were written
in Arabic, most of them were not written by Arabs,
since much like the status of Greek in the Hellenistic
world, Arabic was used as the written language of
non-Arab scholars throughout the Islamic world at the time.
Persians contributed to the world of Mathematics alongside Arabs');
INSERT INTO myarticle(title,article)
VALUES('Medieval European mathematics',
'Medieval European interest in mathematics was driven by
concerns quite different from those of modern mathematicians.
One driving element was the belief that mathematics provided the
key to understanding the created order of nature, frequently
justified by Plato\'s Timaeus and the biblical passage (in the Book of Wisdom)
that God had ordered all things in measure, and number, and weight.[114]
Boethius provided a place for mathematics in the curriculum
in the 6th century when he coined the term quadrivium to describe the
study of arithmetic, geometry, astronomy, and music. He wrote De
institutione arithmetica, a free translation from the Greek of
Nicomachus\'s Introduction to Arithmetic; De institutione musica,
also derived from Greek sources; and a series of excerpts from Euclids Elements.
His works were theoretical, rather than practical, and were the basis of
mathematical study until the recovery of Greek and Arabic mathematical works');
INSERT INTO myarticle(title,article)
VALUES('Chinese mathematics',
'Of particular note is the use in Chinese mathematics of
a decimal positional notation system, the so-called "rod numerals"
in which distinct ciphers were used for numbers between 1 and 10,
and additional ciphers for powers of ten.[76] Thus, the number 123
would be written using the symbol for "1", followed by the symbol for "100",
then the symbol for "2" followed by the symbol for "10", followed by the symbol
for "3". This was the most advanced number system in the world at the time,
apparently in use several centuries before the common era and well before the
development of the Indian numeral system.[77] Rod numerals allowed the representation
of numbers as large as desired and allowed calculations to be carried out on the suan pan,
or (Chinese abacus). The date of the invention of the suan pan is not certain,
but the earliest written mention dates from AD 190, in Xu Yue\'s
Supplementary Notes on the Art of Figures');
First Query for MATCH(column_name) AGAINST(text_to_search)
- The first query we do not get any result
- The second query we get a result
mysql> SELECT id, title, MATCH(title) AGAINST ('china') as title_s
-> FROM myarticle;
+----+-------------------------------+---------+
| id | title | title_s |
+----+-------------------------------+---------+
| 1 | The world history and science | 0 |
| 2 | Islamlic Mathematics study | 0 |
| 3 | Medieval European mathematics | 0 |
| 4 | Chinese mathematics | 0 |
+----+-------------------------------+---------+
4 rows in set (0.00 sec)
mysql> SELECT id, title, MATCH(title) AGAINST ('chinese') as title_s
-> FROM myarticle;
+----+-------------------------------+------------------+
| id | title | title_s |
+----+-------------------------------+------------------+
| 1 | The world history and science | 0 |
| 2 | Islamlic Mathematics study | 0 |
| 3 | Medieval European mathematics | 0 |
| 4 | Chinese mathematics | 1.07391238212585 |
+----+-------------------------------+------------------+
4 rows in set (0.00 sec)
Example 1
mysql> SELECT * FROM myarticle WHERE MATCH(article) AGAINST('Chinese abacus');
--------------------------------------------------------------------+
| Of particular note is the use in Chinese mathematics of
a decimal positional notation system, the so-called "rod numerals"
in which distinct ciphers were used for numbers between 1 and 10,
and additional ciphers for powers of ten.[76] Thus, the number 123
would be written using the symbol for "1", followed by the symbol for "100",
then the symbol for "2" followed by the symbol for "10", followed by the symbol
for "3". This was the most advanced number system in the world at the time,
apparently in use several centuries before the common era and well before the
development of the Indian numeral system.[77] Rod numerals allowed the representation
of numbers as large as desired and allowed calculations to be carried out on the suan pan,
or (Chinese abacus). The date of the invention of the suan pan is not certain,
but the earliest written mention dates from AD 190, in Xu Yue's
Supplementary Notes on the Art of Figures |
--------------------------------------------------------------------+
1 row in set (0.00 sec)
Example 2
mysql> SELECT id, title,
-> MATCH(article) AGAINST('understanding the created order of nature')
-> AS mymatch
-> FROM myarticle
-> WHERE MATCH(article) AGAINST('understanding the created order of nature');
+----+-------------------------------+------------------+
| id | title | mymatch |
+----+-------------------------------+------------------+
| 3 | Medieval European mathematics | 2.30267643928528 |
+----+-------------------------------+------------------+
1 row in set (0.00 sec)
