Optiminzing a query

[Forbes]

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:

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!

[r937]

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"

[Forbes]

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.

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 ;

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 ;

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 ;

[r937]

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

  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

[r937]

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

[Forbes]

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?

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.

[r937]

...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

[Forbes]

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.

[Forbes]

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.