How much slower is...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

How much slower is...

Post 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!
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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######################
Post Reply