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