Two cases have been examined
case 1: simpler
without fullindex - without explain: 0.01s
with fullindex - without explain: 0.01s
without fullindex - with explain: 0.01s
with fullindex - with explain: 0.03s (this is bit controversial)
case 2: bit complex
without fullindex - without explain: 0.03s
with fullindex - without explain: 0.00s
For larger queries, full index make query execution faster....
Code: Select all
mysql> show create table testperformance;
------------------------------------------+
| testperformance | CREATE TABLE `testperformance` (
`title` varchar(25) default NULL,
`body` varchar(25) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.06 sec)
mysql> INSERT INTO testperformance (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
#######################case 1######################
mysql> select * from testperformance
-> where
-> match(title,body) against ('+mysql -tricks use' in boolean mode);
+-----------------------+------------------------------------------+
| title | body |
+-----------------------+------------------------------------------+
| MySQL Tutorial | DBMS stands for DataBase ... |
| How To Use MySQL Well | After you went through a ... |
| Optimizing MySQL | In this tutorial we will show ... |
| MySQL vs. YourSQL | In the following database comparison ... |
| MySQL Security | When configured properly, MySQL ... |
+-----------------------+------------------------------------------+
5 rows in set (0.01 sec)
mysql> explain
-> select * from testperformance
-> where
-> match(title,body) against ('+mysql -tricks use' in boolean mode);
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | testperformance | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)
mysql> show create table testperformance;
---------------------------------------------------------------------------------------+
| testperformance | CREATE TABLE `testperformance` (
`title` varchar(25) default NULL,
`body` varchar(25) default NULL,
FULLTEXT KEY `titlebody` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+------------------------------------------------------------------------------------------1 row in set (0.01 sec)
mysql> select * from testperformance
-> where
-> match(title,body) against ('+mysql -tricks use' in boolean mode);
+-----------------------+------------------------------------------+
| title | body |
+-----------------------+------------------------------------------+
| MySQL Tutorial | DBMS stands for DataBase ... |
| How To Use MySQL Well | After you went through a ... |
| Optimizing MySQL | In this tutorial we will show ... |
| MySQL vs. YourSQL | In the following database comparison ... |
| MySQL Security | When configured properly, MySQL ... |
+-----------------------+------------------------------------------+
5 rows in set (0.01 sec)
mysql> explain
-> select * from testperformance
-> where
-> match(title,body) against ('+mysql -tricks use' in boolean mode);
+----+-------------+-----------------+----------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+----------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | testperformance | fulltext | titlebody | titlebody | 0 | | 1 | Using where |
+----+-------------+-----------------+----------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.03 sec)
#######################case 1######################
Without fullindex:
mysql> select *, match(title,body) against ('+mysql -tricks security ~well' in boolean mode) as relevance
-> from testperformance
-> where
-> match(title,body) against ('+mysql -tricks security ~well' in boolean mode)
-> ORDER BY relevance DESC;
+-----------------------+------------------------------------------+-----------------+
| title | body | relevance |
+-----------------------+------------------------------------------+-----------------+
| MySQL Security | When configured properly, MySQL ... | 1.3333333730698 |
| MySQL Tutorial | DBMS stands for DataBase ... | 1 |
| How To Use MySQL Well | After you went through a ... | 1 |
| Optimizing MySQL | In this tutorial we will show ... | 1 |
| MySQL vs. YourSQL | In the following database comparison ... | 1 |
+-----------------------+------------------------------------------+-----------------+
5 rows in set (0.03 sec)
With fullindex:
mysql> select *, match(title,body) against ('+mysql -tricks security ~well' in boolean mode) as relevance
-> from testperformance
-> where
-> match(title,body) against ('+mysql -tricks security ~well' in boolean mode)
-> ORDER BY relevance DESC;
+-----------------------+------------------------------------------+-----------------+
| title | body | relevance |
+-----------------------+------------------------------------------+-----------------+
| MySQL Security | When configured properly, MySQL ... | 1.3333333730698 |
| MySQL Tutorial | DBMS stands for DataBase ... | 1 |
| How To Use MySQL Well | After you went through a ... | 1 |
| Optimizing MySQL | In this tutorial we will show ... | 1 |
| MySQL vs. YourSQL | In the following database comparison ... | 1 |
+-----------------------+------------------------------------------+-----------------+
5 rows in set (0.00 sec)
#######################case 2######################
#######################case 2######################