Thanks alottttt roger. Right now i m going through the indexes and Full text search stuff. I'll surely let u know any improvement in the performance after i m done.
Binay ----- Original Message ----- From: "Roger Davis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 11, 2003 5:49 PM Subject: RE: Quick ones to speed up acces! > MySQL does not use an index when you do "%blah%". It would use the index > for "blah%" however. See the following reference. > > http://www.mysql.com/doc/en/MySQL_indexes.html > > I would suggest you alter your tables to use a FullText Search. > > http://www.mysql.com/doc/en/Fulltext_Search.html > > Roger > > > -----Original Message----- > > From: Binay Agarwal [mailto:[EMAIL PROTECTED] > > Sent: Sunday, August 10, 2003 7:53 PM > > To: Roger Davis; [EMAIL PROTECTED] > > Subject: Re: Quick ones to speed up acces! > > > > > > Thanks Roger for quick response. > > > > Herebelow the structure of my table and query. > > > > CREATE TABLE npg_search ( > > sno int(11) NOT NULL auto_increment, > > fileid varchar(255) NOT NULL, > > caption text, > > headline varchar(255), > > specialins varchar(255), > > keywords varchar(255) NOT NULL, > > cat varchar(50) NOT NULL, > > byline varchar(255), > > credit varchar(255), > > source varchar(255), > > date date, > > city varchar(100), > > state varchar(100), > > country varchar(100) NOT NULL, > > orgtransref varchar(100), > > copyright varchar(255), > > extratxt text, > > prints tinyint(4), > > extrastr varchar(255), > > bylinetitle varchar(255), > > addedon timestamp(14), > > PRIMARY KEY (sno), > > INDEX (fileid, keywords, cat, country)); > > > > As shown columns fileid,keywords,cat and country are indexed. > > > > Query is: > > > > Select * from table where keywords like '%blah%' or caption like '%blah%' > > or headline like '%blah%' or cat like '%blah%' > > > > Please help me out. > > > > Binay > > > > > > ----- Original Message ----- > > From: "Roger Davis" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Monday, August 11, 2003 4:57 PM > > Subject: RE: Quick ones to speed up acces! > > > > > > > It's called indexing and your queries should return in under 1 > > sec unless > > > you are pulling all your records and all your data. Show us > > your Queries > > > and you table structure and I am sure we can help. > > > > > > Roger > > > > > > > -----Original Message----- > > > > From: Binay Agarwal [mailto:[EMAIL PROTECTED] > > > > Sent: Sunday, August 10, 2003 7:08 PM > > > > To: [EMAIL PROTECTED] > > > > Subject: Quick ones to speed up acces! > > > > > > > > > > > > Hi everybody! > > > > > > > > I am querying from a table containing more than 40,000 records. > > > > Earlier when the records were 10,000 it was taking 9 sec and now > > > > after 1 year and 40,000 records its taking 30 sec. Code is the same. > > > > > > > > I am pretty sure that it has something to do with database only. > > > > No body can think of spending 30 secs for retrieving values from > > > > tables unless it's very huge in the sense of millions of records. > > > > > > > > I don know whether I have to modify my database or do some sort > > > > of restructuring or reindexing so as to make it fast enough. > > > > > > > > Is there some methods or optmization which can be applied to this > > > > database which hasn't been touched since design to enhance the spped. > > > > > > > > If there are some quick ones but valued alottttttt please let me know. > > > > > > > > > > > > Thanks in advance > > > > > > > > Binay > > > > > > > > --- > > > > Incoming mail is certified Virus Free. > > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > > > --- > > > Outgoing mail is certified Virus Free. > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > --- > > Incoming mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.507 / Virus Database: 304 - Release Date: 8/4/2003 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
