Fulltext index and search speed

Question

Question concerning a database / speed issue on a PHP/MySql website. A query with fulltext search seems to be very slow.

I'm not an SQL expert, just simple basic php/html programming for basic websites and somebasic MySQL knowledge. Customer has speed issues for search option on site developed by external programmer not available anymore. We found following info:

MySQL database with a table with approximately 225000 rows (myISAM).
column (DATA) with fulltext index containing data which need to be searched.
This column contains in average 200 words per row.
In phpmyadmin (we only have phpmyadmin acces and ftp no direct server acces) we see that the cardinality of fulltext index is 1.

There is one specific query which seems to be very slow, when in phpmyadmin we execute an EXPLAIN for this query it returns:

id select_type table type possible_keys key key_len ref rows Extra 1
SIMPLE T_SEARCHDATA fulltext DATA DATA 0 1 Using where; Using
filesort 1 SIMPLE TU_CUS const PRIMARY PRIMARY 4 const 1 Using
index 1 SIMPLE M_CATEGORY eq_ref PRIMARY PRIMARY 4
buitenbeeld.T_SEARCHDATA.PC_ID 1 Using index

When executing the query it takes quit a while before the query is executed, the following query is used:

    SELECT T_SEARCHDATA.MID,T_SEARCHDATA.MT_IMG,T_SEARCHDATA.MP_IMG,T_SEARCHDATA.M_ACTIVE,T_SEARCHDATA.TITLE
    FROM T_SEARCHDATA LEFT JOIN TU_CUS ON TU_CUS.UB_ID = 0 LEFT
    JOIN M_CATEGORY ON M_CATEGORY.PC_ID = T_SEARCHDATA.PC_ID
    WHERE MATCH (T_SEARCHDATA.DATA) AGAINST ('+searchword1* +searchword2* +searchword3*' IN BOOLEAN MODE) AND M_ACTIVE = 1 ORDER BY T_SEARCHDATA.UPDATE DESC
    LIMIT 0,30

Our problem

The query above is taking between 5 to even 30 seconds to execute depending on the exact search words. After first time we execute a query the second time it is very fast, some kind of caching seems to be activated???

Some of my more concrete questions:

Is it okay that the cardinality for a fulltext index is 1? Found some answers on this subject but no definite answer on why this would be okay for a full index.
What about speed isn't it strange that a 225000 row database (probably growing to 300000 in coming weeks) is needing this much time to execute this query? Is there something wrong with the query, Can it be optimized?
Is a fulltext search the best option in this case?

I had that problem but i used THINKING SPHINX is very useful — Carlos Morales, Feb 04 '14 at 22:17
Well i don't recommend to use Full Text Search like plugin try to use another way to search — Carlos Morales, Feb 04 '14 at 22:34
Thanks for the answer, I never heard of Sphinx but after reading some more about this I will definitely give this a try. — user3272710, Feb 05 '14 at 12:55

score 1 · Answer 1 · answered Feb 04 '14 at 23:47

1

The cardinality for FULLTEXT indexes seems to be reported as 1 always. Cardinality is not really relevant for fulltext indexes anyway, so the value 1 may just be a placeholder.
Make sure you have enough key_buffer_size to hold the fulltext index. If it has to keep swapping parts of the index in and out of the key buffer during every query, that will kill performance.
Sphinx Search is usually faster than MySQL's fulltext index.

answered Feb 04 '14 at 23:47

Bill Karwin

538,548
86
673
828

Thanks for your answer. I did not knew about Sphinx but after reading a bit more on this I will definitely give this a try. – user3272710 Feb 05 '14 at 12:56

score 0 · Answer 2 · answered Feb 05 '14 at 15:42

0

I recommended you to use Thinking sphinx is very useful.

Here you can find all the informacion:

http://pat.github.io/thinking-sphinx/installing_sphinx.html

answered Feb 05 '14 at 15:42

Carlos Morales

1,137
3
15
38

Fulltext index and search speed

2 Answers2