Wed, 2 Aug 2006

12:04 PM - SQL is a pain in the ass

I'm trying to get this sql query to work where each rss post is unique by user id.  Another words if i post 10 entries i don't want them to be my rss feed of "new" entries.. only my latest and everyone else's.

Here's the problem.. the query i'm working on so far is 17 seconds to run! 

SELECT * from entry WHERE id IN (SELECT MAX(id) as id FROM entry WHERE security=2 GROUP BY uid) ORDER BY date DESC;

This doesn't do much even.  I still have to join this on several other rows etc.  I could start generating the document statically if needed, but still way too slow. 

Anyone got ideas?

4 comments

Comments

Slightly better

userlaffer1
Wed, 02 Aug 2006 04:00:00 +0000

I've added an index and forced them.. its slightly faster.... 17-17.8 seconds now. SELECT * from entry FORCE INDEX(PRIMARY) WHERE id IN (SELECT MAX(id) as id FROM entry FORCE INDEX(entry_uid) WHERE security=2 GROUP BY uid) ORDER BY date DESC Limit 0,15;

WTF?

userlaffer1
Wed, 02 Aug 2006 04:00:00 +0000

Ok i got a crazy idea to make the inner select a view and then sort the results before the outer query did anything SELECT * from entry FORCE INDEX(PRIMARY) WHERE id IN (Select id FROM v_entry_new_unique ORDER By id) ORDER BY date DESC Limit 0,15; Plus the view which was the old inner query. That put me under 1 second!

(no subject)

userlaffer1
Wed, 02 Aug 2006 04:00:00 +0000

The view code DROP VIEW IF EXISTS `jj`.`v_entry_new_unique`; CREATE ALGORITHM=UNDEFINED DEFINER=`laffer1`@`70.91.226.204` SQL SECURITY DEFINER VIEW `v_entry_new_unique` AS select max(`entry`.`id`) AS `id` from `entry` where (`entry`.`security` = 2) group by `entry`.`uid`;

Stored procedure to do the query

userlaffer1
Wed, 02 Aug 2006 04:00:00 +0000

Here is the best version of the query as a stored procedure which calls the previously mentioned view! DELIMITER $$ DROP PROCEDURE IF EXISTS `jj`.`entry_view_latest_rss` $$ CREATE PROCEDURE `jj`.`entry_view_latest_rss` () BEGIN SELECT us.id As id, us.userName, eh.date As date, eh.subject As subject, eh.music, eh.body, mood.title As moodt, location.title As location, eh.id As entryid, eh.security as security, eh.autoformat, eh.allow_comments, eh.email_comments, location.id as locationid, mood.id as moodid FROM user As us, entry As eh, mood, location WHERE eh.id IN (Select id FROM v_entry_new_unique ORDER By id) AND us.id=eh.uid AND mood.id=eh.mood AND location.id=eh.location AND eh.security=2 ORDER BY eh.date DESC, eh.id DESC Limit 0,15; END $$ DELIMITER ;