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
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)