Three queries, same result, huge speed difference

[Uros Kotnik]

executing this SQL, takes ~5 sec.
 
select artists.name , cds.title , tracks.title  from artists, tracks,
cds 
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid 
and MATCH (artists.name) AGAINST ('madonna') 
and MATCH (cds.title) AGAINST ('music') 
and MATCH (cds.title) AGAINST ('mix') 
and MATCH (cds.title) AGAINST ('2001')

and this, ~40 sec.
 
select artists.name , cds.title, tracks.title from artists, tracks, cds 
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid 
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE) 
and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE) 

and executing this takes less than 1 sec.

select artists.name , cds.title, tracks.title from artists, tracks, cds 
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid 
and artists.name like '%madonna%' 
and cds.title like '%music mix 2001%'


Same result but the speed difference is quite a different, why is that ?

This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.


Indexes for table Artists

Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Co
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
--------+------------+---------------+--------------+-------------+---
------------+----------+--------+------+------------+---------+
artists |          0 | PRIMARY       |            1 | artistId    | A
      23806 |     NULL | NULL   |      | BTREE      |         |
artists |          0 | artistIndex   |            1 | name        | A
      23806 |     NULL | NULL   |      | BTREE      |         |
artists |          1 | name          |            1 | name        | A
      23806 |     NULL | NULL   |      | FULLTEXT   |         |
artists |          1 | fulltext_name |            1 | name        | A
      23806 |     NULL | NULL   |      | FULLTEXT   |         |
      
 
Indexes for table CDS      

-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name |
Collation
 Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+----
-------
-------------+----------+--------+------+------------+---------+
| cds   |          0 | PRIMARY        |            1 | cdId        | A
       47199 |     NULL | NULL   |      | BTREE      |         |
| cds   |          1 | cddbIdIndex    |            1 | cddbId      | A
       47199 |     NULL | NULL   |      | BTREE      |         |
| cds   |          1 | artistIndex    |            1 | artistId    | A
       23599 |     NULL | NULL   |      | BTREE      |         |
| cds   |          1 | titleIndex     |            1 | title       | A
       47199 |     NULL | NULL   |      | BTREE      |         |
| cds   |          1 | fulltext_title |            1 | title       | A
       23599 |     NULL | NULL   |      | FULLTEXT   |         |


Indexes for table TRACKS

| Table  | Non_unique | Key_name       | Seq_in_index | Column_name |
Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------------+--------------+-------------+---
--------
+-------------+----------+--------+------+------------+---------+
| tracks |          0 | PRIMARY        |            1 | cdId        | A
|        NULL |     NULL | NULL   |      | BTREE      |         |
| tracks |          0 | PRIMARY        |            2 | trackNo     | A
|      638212 |     NULL | NULL   |      | BTREE      |         |
| tracks |          1 | artistIndex    |            1 | artistId    | A
|       23637 |     NULL | NULL   | YES  | BTREE      |         |
| tracks |          1 | title          |            1 | title       | A
|      212737 |     NULL | NULL   |      | FULLTEXT   |         |
| tracks |          1 | fulltext_title |            1 | title       | A
|      212737 |     NULL | NULL   |      | FULLTEXT   |         |


 
Regards