Posted at 17:28 on November 28th, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | Here's another example of a query which always results in a timeout. In this case, the alternative is even a limitation of functionality. The page in question is the members list. At the moment, only members which have posted at least once are shown. The following query would list all, but it's unusable. If anyone knows how to rewrite this query without a subquery, speak up, please! Code: SELECT m.memberid,m.membername,m.memberstatus,m.registered,count(p.postid) as memberposts FROM sb_members m,sb_posts p WHERE p.poster=m.membername AND p.postedbymember="1" GROUP BY m.membername UNION SELECT memberid,membername,memberstatus,registered,0 FROM sb_members WHERE membername NOT IN (SELECT poster FROM sb_posts) ORDER BY memberposts LIMIT 0,30 ----- Now you see the violence inherent in the system! ----- Edited by Mr Creosote at 17:29 on November 28th, 2006 |
Posted at 18:22 on November 24th, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | I'm using MySQL 5. Here's an example query I used for the topic list of a forum, but which I threw out because of endless timeouts: Code: SELECT topicid, topictitle, topic_views, poll, pinned FROM sb_topics WHERE forum=$forum ORDER BY pinned DESC, (SELECT MAX(posttime) FROM sb_posts WHERE topic = topicid) DESC LIMIT 0,25 ; When I replaced it with the following, it got bearable, but still not very good (that query is currently in use): Code: SELECT t.topicid, t.topictitle, t.topic_views, t.poll, t.pinned, MAX(p.posttime) as posttime FROM sb_topics t, sb_posts p WHERE t.forum=$forum and p.topic=t.topicid GROUP BY p.topic ORDER BY t.pinned DESC, posttime DESC LIMIT 0,25 ; ----- Now you see the violence inherent in the system! |
Posted at 17:35 on November 24th, 2006 | Quote | Edit | Delete | |
Member Dr Gumby Posts: 267 | What version of mysql are you using, I have a couple of pages that use a lot of complex queries but I never had trouble like that. Maybe a missing index could solve a lot of these issues? ----- Lets make this a beefy place |
Posted at 08:27 on November 24th, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | I wouldn't call SQL a 'web language' (it existed long before the WWW), but anyway: I can assure you no functionality has been lost. I just had to split something which can easily be done with one command into something like five, because some of the logic which can be done in SQL, but isn't practically usable in the MySQL implementation because of speed issues, had to be reimplemented in PHP - which is bad programming. Oh well... ----- Now you see the violence inherent in the system! |
Posted at 23:40 on November 23rd, 2006 | Quote | Edit | Delete | |
Member Bachelor Gumby Posts: 73 | Yes - back for good Sorry, but I don't know enough about those "web language" things, so that I'm unfortunatelly unable to discuss about it The only thing that I can "feel" is that it is fast - sorry if you had to deactivate some features to get it run that way Good yourney [edit: moved remarks about the comics to their own thread -Mr Creosote] ----- Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic. ----- Edited by Mr Creosote at 08:33 on November 24th, 2006 |
Posted at 21:10 on November 22nd, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | Hey, long time no see! Thanks, but you pointed your finger to one of the points I'm actually not overly satisfied with: speed. The old board was a lot faster, and I blame that loss of speed on the more complex database queries this version uses. MySQL might be nice for simple stuff, but the query optimizer leaves a lot to be desired when it comes to something more complicated than simple inner joins. For example, it's virtually impossible to use subqueries - they always result into timeouts when used on websites. I had to 'dumb down' the queries a lot to make it usable again at least ----- Now you see the violence inherent in the system! |
Posted at 12:57 on November 22nd, 2006 | Quote | Edit | Delete | |
Member Bachelor Gumby Posts: 73 | Great work! Fantastic Board, fast, simple it looks awesome - oh and it's fast - oh ya I allready mentioned that ----- Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic. |
Posted at 08:11 on November 6th, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | Just checking with Internet Explorer from work, and surprisingly, it looks decent enough. Just very few alignment, padding and border issues, but really very minor, so no work necessary. A general remark on compatibility: The board detects whether the user's browser accepts the XHTML MIME type, and if it does (e.g. Mozilla and Opera), it sends XHTML. If it doesn't (e.g. Internet Explorer, including version 7), it falls back to HTML. Also, I forgot to mention yesterday that I enabled guest posting again with this upgrade. I've taken a simple technical step to make scripted posting harder, so let's see how it turns out. The same method is used for registering. Even before, automatically registered accounts couldn't be used because of e-mail validation, but they could be registered anyway, resulting in lots of unused accounts. Hopefully, this problem will decrease, too. [moved Breaker's error report and my reply here] ----- Now you see the violence inherent in the system! ----- Edited by Mr Creosote at 11:58 on November 6th, 2006 |
Posted at 21:05 on November 5th, 2006 | Quote | Edit | Delete | |
Member Retired Gumby Posts: 964 | Looks very good! |
Posted at 17:11 on November 5th, 2006 | Quote | Edit | Delete | |
Admin Reborn Gumby Posts: 11146 | Well, I guess you've seen the changes already since it should be obvious. This is the long promised rewrite of the forum. There are too many changes to list them all, but you can find an incomplete list under 'Help' - 'Version History'. I also have implemented everything from this thread which had been left open in the first round. Of course, this is still 'beta'. I tested everything briefly, but I'm sure there are different usage patterns which I haven't thought of. So don't hesitate to report bugs! I'm also taking feature requests again, so if you think anything's missing, let me know and I'll consider it. And yes, I'm aware of the irony of putting that much work into the forum when it's basically dead these days. Look at it like this: since there is little action here, I'm not destroying much by putting up a beta version. In any case, I hope you enjoy using it as much as I did writing it ----- Now you see the violence inherent in the system! ----- Edited by Mr Creosote at 17:17 on November 5th, 2006 |