How to fasten MySQL text search.

kaon · 04-29-2007, 08:00 AM

Is it possible to improve text searches? How do other guys achieve FAST searches on their sites?

I have tried two queries but both take 20+ seconds on a database of 2million.

20.957493 seconds
7,603 records

Code:

SELECT P.*, C.TITLE AS CATEGORY, IF (INSTR(P.TITLE, 'football')>0, 1, 0) AS TITLE_REL 
FROM CATEGORY C, PRODUCT P 
WHERE C.ID = P.CATEGORY_ID  AND C.ISVALID='Y' AND P.ISVALID='Y' AND (( P.TITLE LIKE '%football%' ) OR ( P.DETAIL LIKE '%football%' ))   
ORDER BY TITLE_REL DESC, P.STORE_COUNT DESC limit 0, 20;

Above query is using LIKE.

20.945299 seconds
7,537 records

Code:

SELECT P.*, C.TITLE AS CATEGORY, IF (MATCH (P.TITLE) AGAINST ('football'), 1, 0) AS TITLE_REL 
FROM CATEGORY C, PRODUCT P 
WHERE C.ID = P.CATEGORY_ID  AND C.ISVALID='Y' AND P.ISVALID='Y' AND ( MATCH (P.TITLE) AGAINST ('football') OR MATCH (P.DETAIL) AGAINST ('football') ) 
ORDER BY TITLE_REL DESC, P.STORE_COUNT DESC limit 0 , 20

Above query is using MySQL recommended MATCH still not much improvement.

jumpenjuhosaphat · 04-29-2007, 08:21 AM

Try using a full-text index. http://www.phpfreaks.com/tutorials/129/0.php

I don't know much about it, so you'll have to read up on it. Also, with that big of a table, do you have a dedicated MySQL server? You should consider it. There was something that I was reading once about making the full-text index permanently resident in the servers memory. But like I said, I don't know a lot about that.

kaon · 04-30-2007, 09:21 AM

Thanks. I am looking into this tutorial.
We are on dedicated server. However we dont have a separate dedicated server for MySQL.

kaon · 04-30-2007, 09:39 AM

It explains all about fulltext and that we have already done.
I think we have covered everything as far as php or MYSQL query is concerned. The only problem is that it is still too slow.
Now the question is, what else can be done to speed things up.

Quote:

There was something that I was reading once about making the full-text index permanently resident in the servers memory. But like I said, I don't know a lot about that.

Can you tell me more about this?

jumpenjuhosaphat · 04-30-2007, 11:29 AM

Here is an excerpt from a MySQL book :: High Performance MySQL :: http://dev.mysql.com/books/hpmysql-excerpts/ch06.html

coderpl · 04-30-2007, 11:41 AM

Btw. Did you removed duplicated entries in your tables?

kaon · 05-01-2007, 11:20 AM

@jumpenjuhosaphat:
Thanks for the link. I have gone through it but the topic need more time and input. I will read more carefully. May be this is the answer I am looking for.

@coderpl:
Product names are usually (almost certain) unique. But in anycase, what is duplicates effect?

pelinga · 05-10-2007, 05:26 PM

just create index in the tables...
(C.ID ,P.CATEGORY_ID )

kaon · 05-11-2007, 05:34 AM

C.ID is the primary key so I guess is automatically indexed.
P.CATEGORY_ID is indexed already.

pelinga · 05-11-2007, 06:57 AM

( P.TITLE LIKE '%football%' ) OR ( P.DETAIL LIKE '%football%' )
it's the reason that for delay...
never use 'or' in querry.....
you can use 'union'....

kaon · 05-14-2007, 10:40 AM

Can you please tell how to use Union for text search?

04-29-2007, 08:21 AM	#2
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: How to fasten MySQL text search. Try using a full-text index. http://www.phpfreaks.com/tutorials/129/0.php I don't know much about it, so you'll have to read up on it. Also, with that big of a table, do you have a dedicated MySQL server? You should consider it. There was something that I was reading once about making the full-text index permanently resident in the servers memory. But like I said, I don't know a lot about that. __________________ Storage Sheds Lost Forum

04-30-2007, 09:21 AM	#3
kaon Junior Member Join Date: Apr 2007 Posts: 11	Re: How to fasten MySQL text search. Thanks. I am looking into this tutorial. We are on dedicated server. However we dont have a separate dedicated server for MySQL. __________________ PDA Comparison - from online stores including amazon and ebay

04-30-2007, 11:29 AM	#5
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: How to fasten MySQL text search. Here is an excerpt from a MySQL book :: High Performance MySQL :: http://dev.mysql.com/books/hpmysql-excerpts/ch06.html __________________ Storage Sheds Lost Forum

04-30-2007, 11:41 AM	#6
coderpl Member Join Date: Apr 2007 Posts: 219	Re: How to fasten MySQL text search. Btw. Did you removed duplicated entries in your tables? __________________ “ Printing London \| Drukarnia “

05-01-2007, 11:20 AM	#7
kaon Junior Member Join Date: Apr 2007 Posts: 11	Re: How to fasten MySQL text search. @jumpenjuhosaphat: Thanks for the link. I have gone through it but the topic need more time and input. I will read more carefully. May be this is the answer I am looking for. @coderpl: Product names are usually (almost certain) unique. But in anycase, what is duplicates effect?

05-10-2007, 05:26 PM	#8
pelinga Member Join Date: May 2007 Posts: 125	Re: How to fasten MySQL text search. just create index in the tables... (C.ID ,P.CATEGORY_ID ) __________________ Lisisoft::Panload::About-Recipes Calculate Page Rank

05-11-2007, 05:34 AM	#9
kaon Junior Member Join Date: Apr 2007 Posts: 11	Re: How to fasten MySQL text search. C.ID is the primary key so I guess is automatically indexed. P.CATEGORY_ID is indexed already.

05-11-2007, 06:57 AM	#10
pelinga Member Join Date: May 2007 Posts: 125	Re: How to fasten MySQL text search. ( P.TITLE LIKE '%football%' ) OR ( P.DETAIL LIKE '%football%' ) it's the reason that for delay... never use 'or' in querry..... you can use 'union'.... __________________ Lisisoft::Panload::About-Recipes Calculate Page Rank

05-14-2007, 10:40 AM	#11
kaon Junior Member Join Date: Apr 2007 Posts: 11	Re: How to fasten MySQL text search. Can you please tell how to use Union for text search? __________________ PDA Comparison - from online stores including amazon and ebay

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
XML to MYSQL	djjase	MySQL	1	04-05-2007 06:51 PM
All ForumsPR6 Text Link on a Busy Search Engine (Special $10 pm)	burf.com	Links for Sale	3	12-18-2006 09:26 PM
MSSQL or MySQL?	loon	Web Hosting	3	11-20-2006 10:43 AM
MySql	jumpenjuhosaphat	PHP	6	07-10-2006 10:04 PM