How much slower is...
Moderator: General Moderators
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
How much slower is...
How much slower is performing a FULLTEXT query IN BOOLEAN MODE on a column(s) that are not indexed as FULLTEXT, compared to doing so on FULLTEXT indexed column(s). In other words, is it laughable to even perform a fulltext query on non-fulltext indexed columns? Thanks!
I guess the only way to find out is to take a small sample of your data and make two fields, one with the fulltext index and one without and benchmark various match's on each one. What is the issue with storing the index, OR, having a seperate 'index' table that has the fulltext index on the field but your application can still access the normal table without the index (you'd just need to update in both places)"They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow."
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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....
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######################