Hi,
Need to Optimize the below Query. In database around 5,00,000 Records are there. Below query is taking around 1 min. to 1.30 min Execution Time. Need to optimize the query so that the result will be fetch in 5 to 10 Seconds.
We've used full text indexing for Website column and text index on (`Company_name`(10), `Website1`(30), `City`(10), `State`(10), `Country`(10), `cleaned`, `delete`, `junk`, `date_modified`, `approved_date`, `admin_user`).
Query:
SELECT id AS company_id, Company_name, City, State, Country, cleaned, update_user, date_modified AS cleaned_date, delete FROM data_table_new WHERE cleaned = 1 AND delete = 0 AND junk = 0 AND MATCH(Country) AGAINST ('India' IN BOOLEAN MODE) AND date_modified = '2013-04-15' ORDER BY date_modified DESC LIMIT 0, 25
Table Structure:
CREATE TABLE IF NOT EXISTS data_table_new (id bigint(20) NOT NULL AUTO_INCREMENT,Company_name text COLLATE utf8_unicode_ci,Short_Profile text COLLATE utf8_unicode_ci,Telephone text COLLATE utf8_unicode_ci,Alt_Telephone text COLLATE utf8_unicode_ci NOT NULL,Alt_Telephone2 text COLLATE utf8_unicode_ci NOT NULL,Fax text COLLATE utf8_unicode_ci,Alt_Fax text COLLATE utf8_unicode_ci NOT NULL,Mobile text COLLATE utf8_unicode_ci,Alt_Mobile text COLLATE utf8_unicode_ci NOT NULL,Email text COLLATE utf8_unicode_ci,Alt_Email1 text COLLATE utf8_unicode_ci NOT NULL,Alt_Email2 text COLLATE utf8_unicode_ci NOT NULL,Website text COLLATE utf8_unicode_ci,Website1 text COLLATE utf8_unicode_ci,Website2 text COLLATE utf8_unicode_ci NOT NULL,Website3 text COLLATE utf8_unicode_ci NOT NULL,Address text COLLATE utf8_unicode_ci,Address2 text COLLATE utf8_unicode_ci NOT NULL,Gender varchar(16) COLLATE utf8_unicode_ci NOT NULL,Contact_Person_FName text COLLATE utf8_unicode_ci,Contact_Person_LName text COLLATE utf8_unicode_ci NOT NULL,Posting text COLLATE utf8_unicode_ci,City text COLLATE utf8_unicode_ci,Zipcode text COLLATE utf8_unicode_ci,Business_Type text COLLATE utf8_unicode_ci,Year_of_Establishment text COLLATE utf8_unicode_ci,Office_Size text COLLATE utf8_unicode_ci NOT NULL,Legal_Status_of_Firm text COLLATE utf8_unicode_ci,Certification text COLLATE utf8_unicode_ci,Annual_Turnover text COLLATE utf8_unicode_ci,Number_of_Employees text COLLATE utf8_unicode_ci,Export_Percentage text COLLATE utf8_unicode_ci,Main_Markets text COLLATE utf8_unicode_ci,Legal_Owner text COLLATE utf8_unicode_ci NOT NULL,Legal_Representative text COLLATE utf8_unicode_ci,State text COLLATE utf8_unicode_ci,Country text COLLATE utf8_unicode_ci,Banker text COLLATE utf8_unicode_ci,Income_Tax_Registration_No text COLLATE utf8_unicode_ci,Central_Sales_Tax_No text COLLATE utf8_unicode_ci,State_Sales_Tax_No text COLLATE utf8_unicode_ci,Import_Export_Code text COLLATE utf8_unicode_ci,Tax_Identification_No text COLLATE utf8_unicode_ci,SSI_No text COLLATE utf8_unicode_ci,CIN_No text COLLATE utf8_unicode_ci,SCT_No text COLLATE utf8_unicode_ci,RBI_No text COLLATE utf8_unicode_ci,PAN_No text COLLATE utf8_unicode_ci,TAN_No text COLLATE utf8_unicode_ci,EPF_No text COLLATE utf8_unicode_ci,ESI_No text COLLATE utf8_unicode_ci,Service_Tax_Registration_No text COLLATE utf8_unicode_ci,Excise_Registration_No text COLLATE utf8_unicode_ci,Dun_Bradstreet_Number text COLLATE utf8_unicode_ci,Value_Added_Tax_Registration_No text COLLATE utf8_unicode_ci,Industry text COLLATE utf8_unicode_ci,Key_Customers text COLLATE utf8_unicode_ci,DGFT_IE_Code text COLLATE utf8_unicode_ci,Registration_Authority text COLLATE utf8_unicode_ci,Payment_Mode text COLLATE utf8_unicode_ci,Shipment_Mode text COLLATE utf8_unicode_ci,Registration_No text COLLATE utf8_unicode_ci,URL_ID text COLLATE utf8_unicode_ci,Company_Introduction text COLLATE utf8_unicode_ci,Detailed_Type text COLLATE utf8_unicode_ci NOT NULL,Detailed_Profile text COLLATE utf8_unicode_ci NOT NULL,Company_Profile text COLLATE utf8_unicode_ci,Products_Manufacturing_and_Exporting text COLLATE utf8_unicode_ci,Company_Branches text COLLATE utf8_unicode_ci,Services text COLLATE utf8_unicode_ci,Member_Affiliates text COLLATE utf8_unicode_ci,We_Sell text COLLATE utf8_unicode_ci,We_Buy text COLLATE utf8_unicode_ci,Brands text COLLATE utf8_unicode_ci,Factory_Size text COLLATE utf8_unicode_ci,Factory_Location text COLLATE utf8_unicode_ci,QA_QC text COLLATE utf8_unicode_ci,Number_of_Production_Lines text COLLATE utf8_unicode_ci,Number_of_RND_Staff text COLLATE utf8_unicode_ci,Number_of_QC_Staff text COLLATE utf8_unicode_ci,Contract_Manufacturing text COLLATE utf8_unicode_ci,Registered_Location text COLLATE utf8_unicode_ci NOT NULL,Registered_Address text COLLATE utf8_unicode_ci,cleaned tinyint(1) NOT NULL,date_modified date NOT NULL,approved_date date NOT NULL,delete tinyint(1) NOT NULL,junk tinyint(1) NOT NULL,admin_user int(4) NOT NULL,update_user bigint(2) NOT NULL,cr_date datetime NOT NULL,buyer_supplier tinyint(2) NOT NULL COMMENT '0-Supplier,1-Buyer,2-Both',company_represent tinyint(2) NOT NULL DEFAULT '0' COMMENT '0-Yes,1-No',
PRIMARY KEY (id),
KEY search_string (Company_name(10),Website1(30),City(10),State(10),Country(10),cleaned,delete,junk,date_modified,approved_date,admin_user),
FULLTEXT KEY ft_website1 (Website1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
