Re: full text search

Hi Razvan,

I used  MATCH() ... AGAINST also but still performance not improved,... its taking time near about 52 seconds for 1300 records but actual size of our database will be more than 100k records so that's time it wont work Please suggest the any solution to increase speed of accessing data through google app engine application  

On Wednesday, July 18, 2012 10:19:02 PM UTC+5:30, Razvan Musaloiu-E. wrote:

LIKE works with InnoDB tables. Do this tables change often? Could you maintain a copy of them in InnoDB format?

Another idea: why don't you use MATCH() ... AGAINST instead of LIKE?

-- Razvan ME

On Wed, Jul 18, 2012 at 1:09 AM, amber <tekipconsulting@gmail.com> wrote:

Hi,

tables structure are as follows,

CREATE TABLE table1

(UniqueID VARCHAR(255) NOT NULL PRIMARY KEY, Company VARCHAR(255) NULL, Category1 VARCHAR(255) NULL, Category2 VARCHAR(255) NULL, Category3 

VARCHAR(255) NULL, Category4 VARCHAR(255) NULL, Category5 VARCHAR(255) NULL,

 SubCategory1 VARCHAR(255) NULL, SubCategory2 VARCHAR(255) NULL, SubCategory3 VARCHAR(255) NULL, SubCategory4 VARCHAR(255) NULL,

FULLTEXT (Company,Category1,Category2,Category3,Category4)

) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

CREATE TABLE table2 

(SubID VARCHAR(255) NOT NULL PRIMARY KEY , UniqueID VARCHAR(255) NOT NULL FOREIGN KEY REFERENCES table1(UniqueID), COUNTRYCODE VARCHAR(127) NULL, STATUS VARCHAR(255) NULL, TITLE MEDIUMTEXT NULL,  ABSTRACT LONGTEXT NULL, CLAIMS LONGTEXT NULL, PUBLICATIONDATE DATE NULL, EARLIERPRIOTITYDATE DATE NULL, DESCRIPTION LONGTEXT NULL

FULLTEXT (TITLE,ABSTRACT,CLAIMS,DESCRIPTION)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample Query,

select title,SubID from table2 where SubID in(select max(SubID) from table2 where Abstract like '%systemt%' group by UniqueID)

Result:

display the max subid in group of uniqueid records which match 'system' keyword in abstract column 

On Wednesday, July 18, 2012 11:22:07 AM UTC+5:30, Razvan Musaloiu-E. wrote:

Can you run EXPLAIN on one of the queries and examine the query plan? If the query uses temporary tables and the result is too big then mysql will spill the temporary tables to disk and that can cause a significant slowdown.

Some good references on EXPLAIN:

• MySQL Explain – Reference
  
• EXPLAIN Demystified

-- Razvan ME

On Tue, Jul 17, 2012 at 10:39 PM, amber <tekipconsulting@gmail.com> wrote:

Hi all,

thanks to ur quick reply,

i m using nested queries to join to two table and  in googel cloud sql, we stored 100k of records and now while searching through google app its taking too much time and page not display anything.

and in log its showing following error,

This request caused a new process to be started for your application, and thus caused your application code to be loaded for the first time. This request may thus take longer and use more CPU than a typical request for your application. 

On Tuesday, July 17, 2012 12:35:03 PM UTC+5:30, Razvan Musaloiu-E. wrote:

Do you mind providing a dataset and some examples of slow queries? Feel fre to contact me off list for this.

-- Razvan ME

On Mon, Jul 16, 2012 at 11:55 PM, amber <tekipconsulting@gmail.com> wrote:

Hi , 

I imported a DB (~100k rows) on which I am doing full text searches, 

In CloudSQL they are taking more than 30 seconds causing 

Apennine to exceed the request time limit. 

Any suggestions? Or are full text searches just too slow to be done in 

the GCS?