SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Optiminzing a query

    Hi guys!

    Thanks to help I got regarding a question about adding "scoring" option to a table, I've since made the changes, but the query I've ended up with is very slow previously, fractions of a second, whereas now it's 9+ seconds:

    Code:
    SELECT bookmarks.id, links.user_id, bookmarks.url, bookmarks.title, SUBSTR(bookmarks.snippet, 1, 100) as snippet, GROUP_CONCAT(tags.tag) AS tags, bookmarks.datetime, links.status FROM bookmarks, links, tags WHERE (links.status = 'public') AND (links.bookmark_id = bookmarks.id) AND (tags.bookmark_id = bookmarks.id) GROUP BY bookmarks.id ORDER BY bookmarks.datetime DESC LIMIT 0, 9
    Just to recap, the status and user_id columns in the link table were previously in the bookmark table.

    So now, I can better track how many times the same bookmark has been added, and build some kind of ranking system.

    I'm not a MySQL expert, and I'd really appreciate any pointers here on how to speed this query right up!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for each of the tables, so that we can see the indexes that are defined

    what are the relationships between the tables? i mean the one-to-many relationships, which table is the "one" and which table is the "many"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please do a SHOW CREATE TABLE for each of the tables, so that we can see the indexes that are defined

    what are the relationships between the tables? i mean the one-to-many relationships, which table is the "one" and which table is the "many"
    Hi and thanks for the quick reply!

    Many tags relate to one bookmark.

    Many links relate to one bookmark.

    Code:
    CREATE TABLE IF NOT EXISTS `bookmarks` (
      `id` int(11) NOT NULL auto_increment,
      `url` text,
      `title` text,
      `snippet` text,
      `datetime` datetime default NULL,
      PRIMARY KEY  (`id`),
      KEY `datetime` (`datetime`),
      FULLTEXT KEY `title` (`title`,`snippet`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4640 ;
    Code:
    CREATE TABLE IF NOT EXISTS `links` (
      `id` mediumint(11) NOT NULL auto_increment,
      `user_id` mediumint(11) NOT NULL,
      `bookmark_id` int(11) NOT NULL,
      `status` enum('public','private') NOT NULL default 'public',
      UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4627 ;
    Code:
    CREATE TABLE IF NOT EXISTS `tags` (
      `id` mediumint(11) NOT NULL auto_increment,
      `user_id` mediumint(11) NOT NULL,
      `bookmark_id` mediumint(11) NOT NULL,
      `tag` text NOT NULL,
      UNIQUE KEY `id` (`id`),
      FULLTEXT KEY `title` (`tag`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31496 ;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't think i understand your data, or what you're trying to do with that query

    but never mind that, let's look at your two one-to-many relationships in the query, and see where the performance problem might be
    Code:
      FROM bookmarks
         , links
         , tags 
     WHERE (links.status = 'public') 
       AND (links.bookmark_id = bookmarks.id) 
       AND (tags.bookmark_id = bookmarks.id)
    so you're pulling out all public links, tracing them back to their bookmark, and then finding all tags for each bookmark

    notice the two join conditions marked in blue -- neither one of those bookmark_id columns has an index

    add those, try the query again, and if it's still slow, do an EXPLAIN for it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    by the way, the problematic part of your query that i don't understand is this

    multiple links relate to one bookmark, and yet you have a GROUP BY on bookmark.id, which means that all links for a given bookmark are collapsed into one row -- and yet you still have links columns in the SLECT clause, so the particular link that is shown for each bookmark is an indeterminate one, which means that you're getting a random user and a random link status for each bookmark, which i really don't understand why that's useful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't think i understand your data, or what you're trying to do with that query
    OK, just to explain, this is a listing of all the bookmarks for all users, which grabs the tags for each bookmark, as well as resolving who added the bookmark.

    Here's a live version of the Under Cloud, sans the changes I'm working on right now.

    Does that make sense now?

    Quote Originally Posted by r937 View Post
    notice the two join conditions marked in blue -- neither one of those bookmark_id columns has an index

    add those, try the query again, and if it's still slow, do an EXPLAIN for it
    That's certainly brought the query times back down to what they were previously.

    As I said, I'm no MySQL expert; I know enough to hack something into working order. Beyond that, I haunt the halls of forums like this one!

    Thanks for your help.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Forbes View Post
    ...as well as resolving who added the bookmark.
    that's the part that doesn't make sense, because multiple users can add the same bookmark, yeah? so you're just showing one of them, an indeterminate one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by the way, the problematic part of your query that i don't understand is this

    multiple links relate to one bookmark, and yet you have a GROUP BY on bookmark.id, which means that all links for a given bookmark are collapsed into one row -- and yet you still have links columns in the SLECT clause, so the particular link that is shown for each bookmark is an indeterminate one, which means that you're getting a random user and a random link status for each bookmark, which i really don't understand why that's useful
    This is a totally new query, based on the changes to the bookmarks table, and the addition of the links table.

    I've not even begun cursory testing yet, beyond this speed issue. But that's what I'll be doing next, now that the query execution time is coming in under a second.

    Having skimmed through the results so far, everything appears perfectly in order, with the right users being associated with the bookmarks.

    All of the bookmarks are listed in date order, so there won't be any indeterminate issues for these regular listings. However, that may well be an issue for the rankings later on.
    Last edited by Forbes; Feb 13, 2011 at 21:38. Reason: An addition

  9. #9
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's the part that doesn't make sense, because multiple users can add the same bookmark, yeah? so you're just showing one of them, an indeterminate one
    Yes, users can add the same bookmark.

    The intention is to show the first added and the user who added it. How I do that is anyone's guess right now (and most probably another forum question).

    In time, I want to add a ranking algorithm, based on the number of times a bookmark has been added, "liked", used as a relationship etc.


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
  •