Page 1 of 1

How much slower is...

Posted: Wed Jan 18, 2006 9:38 pm
by seodevhead
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!

Posted: Wed Jan 18, 2006 9:54 pm
by josh
"They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow."
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)

Posted: Thu Jan 19, 2006 5:41 am
by raghavan20
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######################