Getting time out error while retrieving large data from MySQL.

[Vandithar]

Hi,

I am using MySQL Version 5.0.45. I am getting time out error while trying to access large data.

I am performing 2 operations:

a) I am loading the data file into the database
b) I am retrieving the data from the database.

1) Loading the data file into the database.

I have 9000 entries in my file. I am using by tab separated delimiter to delimit the field and loading into the database by the following command.

LOAD DATA LOCAL INFILE '/home/group1/cgi-bin/pubmed_abstract/writecatg.txt' INTO TABLE limits_title FIELDS TERMINATED BY '|' (sentences, searchterm, ids)
#Here is the sample data:
Perl is a highly capable, feature-rich programming language with over 22 years of development.| PERL | 32

2) Retrieving the data.

In order to retrieve the data i am using "select" query and free full text searching for the user given query term (User will give search term say "Perl" the records that has term Perl will be retrieved).

I am retrieving data like this:

select * from limits_title where title RLIKE'[[:<:]]perl[[:>:]]';

Most of the times user will give Boolean combination (For Example: PERL OR phython)

The problem now is some times the result will not be displayed or it gives timed out error.

How can i solve this problem?

Is there any method available such that i can fasten the retrieval and loading process?

I am using PERL DBI to connect to MySQL.

Any suggestions?

Regards
Vanditha

[mwasif]

Use MySQL's FULLTEXT searching capability.

[Vandithar]

Use MySQL's FULLTEXT searching capability.

Hi,

Thanks for the reply.

I tried using that but i was not getting desired results.

How can i make searching and inserting faster?

Any other methods?

Regards

[mwasif]

I tried using that but i was not getting desired results.

Can you explain this?

[Vandithar]

Can you explain this?

Hi,

When i used Match against operator in Boolean mode i was not getting exact match for the user specified query where as RLIKE operator did gave me exact match.

One more important question here is about inserting the data. How can i insert the data into the database in a faster way?

I am using full text on table. Is there any changes to be made in my.cnf file?

How can i insert and retrieve in a faster way?

Regards
Vanditha

[mwasif]

Using double quotes around search string gives an exact match.

[Vandithar]

Using double quotes around search string gives an exact match.

Hi,

How can insertion of data in to the database can be speed n up?

I am using full text.

How can the time taken to insert the data into the database can be reduced?

Regards
Vanditha