On Wed, Aug 13, 2003 at 12:58:06AM +0530, Binay Agarwal wrote: > Hi > > While using OR in the query, Mysql doesn't use indexes.. right? Hence takes more > time. > > If queries have to be ORed on various coloumns (demand of the application) what one > can do to speed it up. > > Table structure: > > 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 (not the composite > indexed). > > Query is: > > Select * from table where keywords like '%blah%' or caption like '%blah%' or > headline like '%blah%' or cat like '%blah%' limit 20,10 > > > Note: I can not use keywords like 'blah%' it has to be '%blah%' only... (i.e balh > can be even middle part of the words) > > Table contains 50,000 records and queries taking 7 sec which is unbelievable. > > Mysql version: 3.23.52 > > Please suggest me some solution for this.
It's not the OR that's doing it. IT's the %blah% queries. Look at MySQL's FULLTEXT indexes. I suspect they'll help A LOT. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 391,895,882 queries (392/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
