Bug Reports · Legacy archive
*Raises a boot*
-
*Raises a boot*
#1Never know when a bug is going to hit. Tried to upvote someone's comment and the server said 'STFU':
A Database Error Occurred
Error Number: 2008
MySQL client ran out of memory
SELECT *, `a`.`level` FROM (`lockmor_user` as a) JOIN `lockmor_level` as b ON `a`.`level` = `b`.`level_id` JOIN `smf_members` as c ON `a`.`author_id` = `c`.`id_member` WHERE `a`.`author_id` = '1636'
Filename: /home/strolen/public_html/models/mlogin.php
Line Number: 147
-
Re: *Raises a boot*
#2Memory?!?!?!?
Unusual. Come middle of the month I will use the uptime data and see about optimizing a bit. For these, just beat it into submission! -
Re: *Raises a boot*
#3Maybe the member count just exceeded some threshold.
Do you really need *? Extra unused columns consume extra resources.
* is good for adhoc queries, but you really should just ask for what you need :)
-
Re: *Raises a boot*
#4
Maybe the member count just exceeded some threshold.
Do you really need *? Extra unused columns consume extra resources.
* is good for adhoc queries, but you really should just ask for what you need :)
That sounds about right. Keep in mind, you're essentially pulling three complete tables, joining them together, and returning all of it in one chunk. It wouldn't shock me if that annihilated the memory constraints, depending on how many cells you have in the combined tables.
You might try moving the WHERE clause into a subquery on the lockmor_user table. That would reduce the memory load significantly, since the subsequent JOINs will only return columns that correspond to the single user returned from the subquery.
Something like this, I think:
SELECT *, `a`.`level` FROM ((SELECT * FROM `lockmor_user` WHERE `author_id` = '$id') as a JOIN `lockmor_level` as b ON `a`.`level` = `b`.`level_id` JOIN `smf_members` as c ON `a`.`author_id` = `c`.`id_member`)
-
Re: *Raises a boot*
#5Ya know what, you are absolutely right!
All said though, there aren't that many columns in those three particular tables. The user table, I need all of that one...all it has is level, XP, votes and all that stuff that is shown at the top of the page about you. The level table only has the title and an id in it. The _real_ the waste is in the smf_members table. All I really need from there is your member name and PM check, but there are quite a few needless columns returned as you suggested. Nothing that should have keyed a memory error though, not by a long shot!
Original query: Query took 0.0007 sec
Modified Query by Chaos: Query took 0.0006 sec
(you know I had to take a quick look ;) )
Upon multiple retries they both took 0.0003 so thinking they might have been cached.
I see your point and it makes sense but I think MySQL may be smarter than that and fixes my query to pull it as you described. I don't know if .0001 is significant without some batch testing. Either way, you got a very valid point logically!
Where the real waste is ,and I cringe when I work on it, is that on every page load is I am forced to load a shit ton of forum includes which hold a bunch of useless queries just to maintain the login persistence throughout the site. It is a convenient, all encompassing include from the forum makers, not a optimized authorization plugin.
http://www.simplemachines.org/community/ssi_examples.php
While it doesn't automatically load all that crap shown (most are separate functions), there is enough in there that gets loaded so far beyond what I need that it gives me the shakes. Now that they went gold on their forum and the base code has stabilized, I will eventually trim it down to the necessities, but the nature of the forum still requires quite a bit of useless action (from the main sites POV) even after I trim the fat. Nature of using the forums auth functions though. And they are quite handy I must admit.
I really need some QA because I *know* there is a bunch of optimization that can be done throughout the code. I do reviews of sections occasionally but not nearly as much as I could. Too much fun writing new stuff!!
Awesome stuff though, thanks for taking the time and looking at it. And since that query is on every load, overall it will be worth fixing it up! -
Re: *Raises a boot*
#6Remembering my databases class a few semesters back, I believe optimizing table queries is NP Hard. There's only so much you can do with it; for that particular query, yeah, it's probably smart enough to optimize it, but I wouldn't trust it to. Of course, for such a lightweight query, it's almost not worth the trouble; that one took a handful of milliseconds to run.