Very slow query

I have a database with over 12 million records that the user can run a search on, but it is taking far too long. Can anybody help me try and optimize my SQL to work fast? I can post any additional information that you may need. Thanks.

It’s hard to help you if we have to guess your circumstances :slight_smile:

Please post the relevant queries here and do a SHOW CREATE TABLE for each table concerned.

Here is the Table information:

CREATE TABLE Businesses (
ID int(11) NOT NULL auto_increment,
Code varchar(11) NOT NULL,
Description varchar(255) NOT NULL,
Company varchar(255) NOT NULL,
Contact varchar(255) NOT NULL,
Title varchar(255) NOT NULL,
Address varchar(255) NOT NULL,
City varchar(255) NOT NULL,
County varchar(255) NOT NULL,
State varchar(2) NOT NULL,
Zip varchar(11) NOT NULL,
Phone varchar(20) NOT NULL,
Fax varchar(20) NOT NULL,
Email varchar(255) NOT NULL,
Website varchar(255) NOT NULL,
Member int(1) NOT NULL default ‘0’,
Spotlight int(1) NOT NULL default ‘0’,
Logo varchar(255) NOT NULL,
Spotlight_Copy varchar(255) NOT NULL,
Bonus int(1) NOT NULL default ‘0’,
PRIMARY KEY (ID),
KEY Member (Member,Spotlight),
FULLTEXT KEY Company (Company),
FULLTEXT KEY State (State)
) ENGINE=MyISAM AUTO_INCREMENT=12649777 DEFAULT CHARSET=utf8

Here is my search query:

SELECT Company FROM Businesses WHERE MATCH (Company) AGAINST (‘" & s & "’) GROUP BY Company

Why are you using GROUP BY company?
It looks unnecessary to me as it is the only thing being returned anyway. Delete it and see if you get the same data returned.

And then try WHERE company LIKE(whatever)

(I thought MATCH was for more complex searches than this - eg to include one thing and exclude another, but I’ve never actually had a use for it, so I could be wrong)