#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Optimize the Query


    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`,`d ate_modified`,`approved_date`,`admin_user`),
    FULLTEXT KEY `ft_website1` (`Website1`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,004
    Rep Power
    9398

    Moved from PHP


    ohsweetjesus

    Looks like you have a normalization problem. I don't even know where to start... Exactly what kind of data are you storing in this table? And why, for the love of God, is everything a text field?

    As for the query, what does an EXPLAIN on it produce?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    add an index on date_modified

    don't use MATCH on country, use equals

    by the way, your ORDER BY is useless, since there is only one date_modified that will be returned

    also, get rid of those TEXT datatypes and use VARCHARs instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo