MySQL Forums
Forum List  »  Performance

Slow search query
Posted by: JCA JCA
Date: January 03, 2009 08:30PM

Hi folks,

Hopefully someone can help. I'm creating a (theoretically) simple site where people can submit news stories and vote those stories up or down. I have a query that looks like this:
SELECT SQL_CALC_FOUND_ROWS stories.storyID, users.userName, stories.addDate,
stories.headline, stories.synopsis, stories.storyURL, stories.upCount,
stories.downCount, stories.score, stories.reviewed, stories.expired,
activityLog.actionID AS votedAlready
FROM stories 
LEFT JOIN users ON stories.userID=users.userID
LEFT JOIN activityLog ON stories.storyID=activityLog.recordNum AND activityLog.userID=2
AND (activityLog.actionID=3 OR activityLog.actionID=4)
WHERE 1 GROUP BY stories.storyID ORDER BY stories.score DESC, stories.storyID DESC LIMIT 0, 5

In English, I think this query selects relevant fields for all stories in the db, including a field indicating whether the logged in user (ID=2) has already voted (up=3, down=4) on the returned stories. Results are sorted in this case by score and then 'newness', and are then limited for pagination.

My description could be wrong about what this query is actually doing so feel free to set me straight! :)

The "WHERE 1" is sometimes replaced by conditions on actionID, or a LIKE on userName.

My db currently has ~25 users, ~1000 stories, and ~2000 entries in the activity log. The log is updated with each log-in, story upload, and vote.

For some reason this query takes ~1.8 seconds. It used to take 22+, but addition of an index on some of the query fields has helped.. I currently have indexes on: activityLog.actionID, stories.score, stories.userID, as well as primaries on each of the tables (activityLog.activityID, stories.storyID, and users.userID). I know, I know... actionID (not unique) and activityID (unique and autoincrementing) are confusing, but that's what I've got right now. :)

EXPLAIN of the above query gives me:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	stories 	ALL 	NULL 	NULL 	NULL 	NULL 	1000 	Using temporary; Using filesort
1 	SIMPLE 	users 	eq_ref 	PRIMARY 	PRIMARY 	4 	db.stories.userID 	1 	 
1 	SIMPLE 	activityLog 	ALL 	al_ix_actionid 	NULL 	NULL 	NULL 	2009

Any ideas how to speed this up? This query works well for me in that the same basic query structure works for all the sort cases I need etc. If it would be wiser I could add some logic back in the php code to create completely different queries for different cases, or do multiple queries if necessary to get my final result set. I'd rather offload the work to MySQL if it can be made fast enough.

No reason a simple job like what I'm doing should take more than a couple 100ths of a second with the number of records I have.

Cheers, and thanks in advance!
--
Justin

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow search query
3534
January 03, 2009 08:30PM
1711
January 07, 2009 03:07PM
1633
January 12, 2009 01:08AM
1706
January 12, 2009 01:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.