12:04 PM - SQL is a pain in the ass
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?
Comments
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;
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!
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`;
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 ;