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]
