MySql DBA Project 2

Description

MySql DBA Project 2

Sometimes a search isn’t thorough. For example, when looking for information on guitars, a user may search for the term “guitars” and potentially miss documents containing the word “Fender” (a type of guitar).

MySQL’s solution to this problem is the WITH QUERY EXPANSION option. The syntax for this solution is as follows:

SELECT Title, AlbumDescription FROM Albums
WHERE MATCH (AlbumDescription)
AGAINST (‘guitars’ WITH QUERY EXPANSION);

When you use WITH QUERY EXPANSION, MySQL actually searches your full text index twice. The first time it searches with the word guitars. It then takes the most relevant results from the first search, and searches again. Take a look:

mysql> SELECT Title, AlbumDescription FROM Albums
-> WHERE MATCH (AlbumDescription)
-> AGAINST (‘guitars’);
+———————+——————————————+
| Title | AlbumDescription |
+———————+——————————————+
| Blood on the Tracks | Recorded using Martin and Fender guitars |
+———————+——————————————+
1 row in set (0.00 sec)

mysql> SELECT Title, AlbumDescription FROM Albums
-> WHERE MATCH (AlbumDescription)
-> AGAINST (‘guitars’ WITH QUERY EXPANSION);
+———————-+——————————————+
| Title | AlbumDescription |
+———————-+——————————————+
| Blood on the Tracks | Recorded using Martin and Fender guitars |
| Highway 61 Revisited | First using the fender! |
+———————-+——————————————+
2 rows in set (0.00 sec)

In the second query, the first row was matched by guitars, and the word fender was used to find the second row.

Demonstrate WITH QUERY EXPANSION by writing four new subqueries against your Albums table. In your first two queries, do not use WITH QUERY EXPANSION. In the remaining two queries DO use WITH QUERY EXPANSION.

Combine the results of the four subqueries with a UNION. Add a column to the result set called QueryExpansion that indicates which row used query expansion. Also, add a column called Query to the result to indicate which words were included in your search.

Save your query as dba1lesson11project2.sql and then hand in the project.