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!
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 ([COLOR="Blue"]links.bookmark_id = bookmarks.id[/COLOR])
AND ([COLOR="blue"]tags.bookmark_id = bookmarks.id[/COLOR])
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
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
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.
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
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.
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.