SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Optimizing the query with respect to doctrine

    I a newbie in mysql database, but i have a requirement like, to optimize the below query with respect to usage of doctrine and to get a quick response

    The query is:

    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' in BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en

    Thanks in advance
    regards,
    kavitha:

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please to a SHOW CREATE TABLE for each table involved in the query, and do an EXPLAIN for the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by srija View Post
    I a newbie in mysql database, but i have a requirement like, to optimize the below query with respect to usage of doctrine and to get a quick response

    The query is:

    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' in BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en

    Thanks in advance
    regards,
    kavitha:
    Hello sirja,
    Welcome to the forums.
    Pease read the forum guidelines before posting. you should make a habit of posting code with proper wrapping. In this case use as:
    Code SQL:
    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' IN BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en
    Note proper formatting in itself also make the code cleaner and easier to read.
    Thanks

  4. #4
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please to a SHOW CREATE TABLE for each table involved in the query, and do an EXPLAIN for the query
    This is the output of the explain plan of the above query

    HTML Code:
    id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
    1 PRIMARY y ALL NULL NULL NULL NULL 211009 Using where; Using filesort 
    2 DEPENDENT SUBQUERY y2 ref company_id company_id 4 uae2009.y.company_id 3 Using where; Using index 
    2 DEPENDENT SUBQUERY y3 eq_ref PRIMARY,keyword_en,keyword_en_2 PRIMARY 4 uae2009.y2.keyword_id 1 Using where
    thanks in advance
    regards

  5. #5
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    Hello sirja,
    Welcome to the forums.
    Pease read the forum guidelines before posting. you should make a habit of posting code with proper wrapping. In this case use as:
    Code SQL:
    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' IN BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en
    Note proper formatting in itself also make the code cleaner and easier to read.
    Thanks


    Code MySQL:
    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' in BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en

  6. #6
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    Hello sirja,
    Welcome to the forums.
    Pease read the forum guidelines before posting. you should make a habit of posting code with proper wrapping. In this case use as:
    Code SQL:
    SELECT DISTINCT y.company_id AS y__company_id, y.service_price AS y__service_price, y.print_service AS y__print_service, y.company_name_en AS y__company_name_en, y.gps_latitude AS y__gps_latitude, y.gps_longitude AS y__gps_longitude, y.company_name_ar AS y__company_name_ar, y.url AS y__url, (y.service_price4 + IFNULL((SELECT SUM(y2.revenue_actual) AS y2__0 FROM yp_comp_keywords_revenue y2 INNER JOIN yp_keyword_master y3 ON y2.keyword_id = y3.keyword_id WHERE ((y3.keyword_en = CONCAT("trading",'') OR FIND_IN_SET( CONCAT("trading",''),keyword_synonyms_en)) AND y2.company_id = y.company_id AND y2.start_date <= curdate() AND y2.end_date >= curdate()) GROUP BY y2.company_id), 0)) AS y__0, (y.print_service + 0) AS y__1, y.company_name_en AS y__2, y.keywordmaster_en AS y__3, y.company_keywords_en AS y__4, y.keyword_hide_en AS y__5 FROM yp_companies y WHERE 1 AND (MATCH(company_name_en,company_synonyms_en) AGAINST('"trading"' IN BOOLEAN MODE) OR FIND_IN_SET( "trading" , company_keywords_en)) HAVING (y__0 > 0 OR y__1> 0) ORDER BY y__0 DESC, y.print_service DESC, y.company_name_en
    Note proper formatting in itself also make the code cleaner and easier to read.
    Thanks

    These are indexes i have in the table yp_companies
    TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT
    NULL uae2009 yp_companies 0 uae2009 company_id 1 company_id A 211009 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 area_id 1 area_id A 433 NULL NULL YES BTREE
    NULL uae2009 yp_companies 1 uae2009 city_id 1 city_id A 23 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 directory_id 1 directory_id A 8 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 service_price 1 service_price A 937 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 service_price1 1 service_price1 A 837 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 print_service 1 print_service A 1 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 service_price3 1 service_price3 A 552 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 service_price4 1 service_price4 A 548 NULL NULL BTREE
    NULL uae2009 yp_companies 1 uae2009 company_name_en_2 1 company_name_en NULL 1 NULL NULL FULLTEXT
    NULL uae2009 yp_companies 1 uae2009 index_synonyms_en 1 company_synonyms_en NULL 1 NULL NULL YES FULLTEXT
    NULL uae2009 yp_companies 1 uae2009 index_synonyms_en 2 company_name_en NULL 1 NULL NULL FULLTEXT
    how to post the result of a query in the table form properly
    Thanks in advance
    regards
    kavitha

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you please do a SHOW CREATE TABLE for each table
    Code:
    CREATE TABLE `yp_companies` (
                         `company_id` int(11) NOT NULL default '0',
                         `company_name_en` varchar(100) NOT NULL,
                     `company_name_ar` varchar(100) character set utf8 NOT NULL,
                      `address_en` varchar(200) NOT NULL,
                      `address_ar` varchar(200) character set utf8 NOT NULL,
                      `pobox_name_en` varchar(100) character set utf8 NOT NULL,
                      `pobox_name_ar` varchar(100) character set utf8 NOT NULL,
                      `office_en` varchar(255) character set utf8 NOT NULL,
                       `office_ar` varchar(255) character set utf8 NOT NULL,
                        `floor_en` varchar(255) character set utf8 NOT NULL,
                        `floor_ar` varchar(255) character set utf8 NOT NULL,
                        `building_number` varchar(255) character set utf8 NOT NULL,
                   `building_en` varchar(255) character set utf8 NOT NULL, 
                    `building_ar` varchar(255) character set utf8 NOT NULL,
                      `street_en` varchar(255) character set utf8 NOT NULL,
                       `street_ar` varchar(255) character set utf8 NOT NULL,
                        `landmark` varchar(100) NOT NULL,
                        `landmark_ar` varchar(255) character set utf8 NOT NULL,
                        `company_logo` varchar(255) default NULL,
                        `emailid` varchar(50) default NULL,
                        `area_id` int(11) default '0',
                         `city_id` int(11) NOT NULL,
                         `directory_id` int(11) NOT NULL,
                        `parent_company` int(11) default '0',
                         `gps_latitude` varchar(20) default NULL,
                         `gps_longitude` varchar(20) default NULL,
                         `service_price` float(15,2) NOT NULL default '0.00',
                         `service_price1` float(15,2) NOT NULL default '0.00',
                         `service_price3` float(15,2) NOT NULL,
                         `service_price4` float(15,2) NOT NULL, 
                         `company_synonyms_en` text,
                        `company_synonyms_ar` text character set utf8,
                        `company_keywords_en` text NOT NULL,
                        `company_keywords_ar` text character set utf8 NOT NULL,
                       `keywordmaster_en` text NOT NULL,
                        `keywordmaster_ar` text character set utf8 NOT NULL,
                        `keyword_hide_en` text character set utf8 NOT NULL,
                         `keyword_hide_ar` text character set utf8 NOT NULL,
                         `print_service` smallint(6) NOT NULL,
                         `mole` int(11) NOT NULL default '0',
                          `url` varchar(150) NOT NULL,
                          `deal` varchar(150) NOT NULL default '0',
                          `deal_type` varchar(150) NOT NULL default '0',
                          `added_by` int(11) NOT NULL,
                          `added_date` datetime NOT NULL,
                          `modified_by` int(11) NOT NULL,
                          `modified_date` datetime NOT NULL,
                          UNIQUE KEY `company_id` (`company_id`),
                           KEY `area_id` (`area_id`),
                           KEY `city_id` (`city_id`),
                           KEY `directory_id` (`directory_id`),
                           KEY `service_price` (`service_price`),
                           KEY `service_price1` (`service_price1`),
                           KEY `print_service` (`print_service`),
                           KEY `service_price3` (`service_price3`),
                           KEY `service_price4` (`service_price4`),
                FULLTEXT KEY `company_name_en_2` (`company_name_en`),
                FULLTEXT KEY `index_synonyms_en` 
               (`company_name_en`,`company_synonyms_en`))
            ENGINE=MyISAM DEFAULT CHARSET=latin1

    Code:
    CREATE TABLE `yp_comp_keywords_revenue` (`comp_key_rev_id` int(10) NOT NULL auto_increment,
       `company_id` int(10) NOT NULL,
       `keyword_id` int(10) NOT NULL,
       `issue_year` varchar(4) NOT NULL,
       `start_date` datetime default NULL,
       `end_date` datetime default NULL,
       `revenue_actual` double default NULL, 
       `revenue_total` double NOT NULL default '0',
       `added_by` int(11) NOT NULL,
       `added_date` datetime NOT NULL,
       `modified_by` int(11) NOT NULL,
       `modified_date` datetime NOT NULL,
       PRIMARY KEY  (`comp_key_rev_id`),
       KEY `company_id` (`company_id`,`revenue_actual`,`keyword_id`,`start_date`,`end_date`))    ENGINE=MyISAM AUTO_INCREMENT=52885 DEFAULT CHARSET=utf8

    Code:
    CREATE TABLE `yp_keyword_master` (`keyword_id` int(11) NOT NULL auto_increment,
     `keyword_en` varchar(100) NOT NULL,
      `keyword_ar` varchar(100) NOT NULL,
      `keyword_synonyms_en` text NOT NULL,
      `keyword_synonyms_ar` text NOT NULL,
      `added_by` int(11) NOT NULL,
      `added_date` datetime NOT NULL,
     `modified_by` int(11) NOT NULL,
     `modified_date` datetime NOT NULL,
      `is_hide` smallint(6) NOT NULL default '0',
      `status` smallint(6) NOT NULL,
      `is_lock` smallint(6) NOT NULL,
      PRIMARY KEY  (`keyword_id`),
      KEY `keyword_en` (`keyword_en`),
      KEY `keyword_ar` (`keyword_ar`),
      KEY `is_hide` (`is_hide`),
      FULLTEXT KEY `keyword_en_2` (`keyword_en`,`keyword_synonyms_en`),
      FULLTEXT KEY `keyword_ar_2` (`keyword_ar`,`keyword_synonyms_ar`)) ENGINE=MyISAM   AUTO_INCREMENT=4159 DEFAULT CHARSET=utf8

    These are my three tables
    IF i remove the order by clause in the above query , the query is little optimized but i am in need of the order by clause

    Please help me to find a solution

    thanks in advance
    kavitha

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for all the info

    i can't figure out your query, it's way too complicated for me

    the only thing i can think of is to remove the DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    thanks for all the info

    i can't figure out your query, it's way too complicated for me

    the only thing i can think of is to remove the DISTINCT
    I have found out the reason for long execution time of the query.

    The reason is : using a alias name in the order by clause makes the optimizer not to use the index for the order by , there by doing full table scan ie filesort method is used.

    Please tell me how to provide a solution for it????

    Thanks in advance
    regards
    kavitha


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •