SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    The Netherlands
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can this query be optimized further?

    I have the following query:
    Code:
    SELECT 
          news_id AS id, 
          start_date, 
          CONCAT( news_title, " )", COUNT( reaction_id ) , ")" ) AS title
    FROM newsitems
    LEFT OUTER JOIN 
          reactions ON ( 
                 newsitems.news_id = reactions.article_id 
                 AND reactions.article_type = 'news' )
    WHERE 
          start_date <=1082407014 
    AND (
          end_date IS NULL 
          OR 
          end_date >=1082407014
    )
    GROUP BY 
          news_id, 
          start_date
    ORDER BY 
          start_date DESC , 
          news_id DESC
    LIMIT 10
    The tables look like this:
    Code:
    CREATE TABLE `newsitems` (
      `news_id` int(10) unsigned NOT NULL auto_increment,
      `news_title` varchar(255) NOT NULL default '',
      `news_author` varchar(60) NOT NULL default '',
      `news_source_name` varchar(60) NOT NULL default '',
      `news_source_url` varchar(255) NOT NULL default '',
      `start_date` bigint(20) unsigned NOT NULL default '0',
      `end_date` bigint(20) unsigned default '0',
      `is_breaking_news` int(11) NOT NULL default '0',
      `bn_image` int(11) NOT NULL default '0',
      `edit_summary` text NOT NULL,
      `edit_body` text NOT NULL,
      PRIMARY KEY  (`news_id`),
      FULLTEXT KEY `news_title` (`news_title`),
      FULLTEXT KEY `edit_summary` (`edit_summary`),
      FULLTEXT KEY `edit_body` (`edit_body`),
      KEY `start_date` (`start_date`),
      KEY `end_date` (`end_date`),
      KEY `news_title_2` (`news_title`)
    ) TYPE=MyISAM;
    Code:
    CREATE TABLE `reactions` (
      `reaction_id` int(10) unsigned NOT NULL auto_increment,
      `article_id` int(10) unsigned NOT NULL default '0',
      `article_type` enum('news','reviews') NOT NULL default 'news',
      `reaction_body` text NOT NULL,
      `reaction_datetime` bigint(20) NOT NULL default '0',
      `poster_name` varchar(30) NOT NULL default '',
      `poster_email` varchar(50) default NULL,
      PRIMARY KEY  (`reaction_id`,`article_id`,`article_type`)
    ) TYPE=MyISAM
    I think I have improved the speed already by adding some indexes, but if I "explain" the query, I get the following result:
    Code:
     table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
    newsitems 	ALL 	start_date,end_date 	NULL 	NULL 	NULL 	841 	where used; Using temporary; Using filesort
    reactions 	index 	NULL 	PRIMARY 	9 	NULL 	2756 	Using index
    According to the MySQL manual, it's a Bad Thing if you have Using temporary and/or Using filesort in the "Extra" column, so if it's possible I want to get rid of it.

    Of course, if there is a better way to get the same results, please let me know

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not sure i can help you with the performance tuning
    but i notice that you are not grouping by all your non-aggregates

    it should be:

    ... GROUP BY news_id, start_date, news_title

    also, reverse the parenthesis after the &nbsp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •