WOAH!

I think I just hit jackpot trying to figure out the slow server problem. =)

The queries on this site are generally highly-oiled, custom-made, slick-as-a-stick queries.

I found a whopper of a bug that showed up when logged in members viewed a message board that caused the database to load all 30,000 or so favorite boards by everyone instead of just your own in one particular query.

Even worse, each of those 30,000 favorite boards were attached to individual messages. It's no wonder AQ was coming to its knees under the strain.

I put in the fix--two lines of code changed--and the load on the server immediately dropped from about 17 to 0.32. A 98% percent improvement. =)

Yee-ha! The site should be smoking fast now, and--knock on wood--a long time to come. =)

-- Ryan

Quote

I put in the fix--two lines of code changed--and the load on the server immediately dropped from about 17 to 0.32. A 98% percent improvement. =)

Yee-ha! The site should be smoking fast now, and--knock on wood--a long time to come. =)

Don't cha just love it when a simple solution comes along and makes all the wheels turn so smoothly! BRAVO!!!

Btw, I was doing a "search messages" today and it gave me literally hundreds of results. I know that had to put a strain on AQ. I was just doing a simple search and never expected so many results. Heck, even searching for "George" brings up two full pages of messages from all boards. Maybe you could add a "Boards" option, to choose which boards to search.

Thanks for the consideration,
castles16

Quote

Btw, I was doing a "search messages" today and it gave me literally hundreds of results. I know that had to put a strain on AQ. I was just doing a simple search and never expected so many results. Heck, even searching for "George" brings up two full pages of messages from all boards. Maybe you could add a "Boards" option, to choose which boards to search.

A "boards" option is actually on my to do list, but it wouldn't actually help the database much in this particular case. The messages are stored in a FULLTEXT index, and if you read the small print in the documentation (which I do, of course, since there's all sorts of tips and tricks to help know what changes might help improve the performance of the database), it says that it won't use any other index except the FULLTEXT one first. You can search on a specific board, but AQ will still first find all the posts with your search terms, then cull out the boards you're interested in.

Message board searches are actually pretty efficient, all things considered. =) When you FIRST do a search, it may take a few seconds for the system to load the index into memory, but once loaded into memory, it's pretty darned fast.

It's even more noticeable if you try to search letterbox clues for keywords. That index is absolutely massive (about 25 megabytes, last time I checked several months ago--it's certainly a lot larger now!) and doesn't get used very often, so it almost always needs to be loaded into memory first before it can be searched. So the first search of a clue's keywords might take several seconds while the index is being loaded into memory, but subsequent searches are practically instantaneous after that.

-- Ryan

Quote

I put in the fix--two lines of code changed--and the load on the server immediately dropped from about 17 to 0.32. A 98% percent improvement. =)

That is an AWESOME improvement:)

Thanks again Ryan:)

T;)

Quote

I think I just hit jackpot trying to figure out the slow server problem. =)

Well done Ryan. I too was really worried there for a moment.

Your site is amazing and I don't know how you come up with so much so quickly. I know this was a lucky bug, but to be able to find it is wonderful in itself.

3 cheers for Ryan!

YT

Quote

3 cheers for Ryan!

Hip Hip Hurray!!
Hip Hip Hurray!!
Hip Hip Hurray!!

Freddy's not coming after me tonight with nightmares of an AQ-less world.
AQ-less would mean I'd be clue-less <------ hehe...dixie made joke...hehe

Quote

I put in the fix--two lines of code changed--and the load on the server immediately dropped from about 17 to 0.32. A 98% percent improvement. =)

I don't know what all this means. I take computers for granted. And this coming from a girl that has lived long enough to know what a Betamax video player is, who's family had one of the first microwaves, a desktop computer wasn't even a glimmer in someone's eye.

I know people are always saying thank you. Before, I've let their thanks speak for me to.

But, while I was reading these posts and seeing Dixie's post I started thinking. How would we look for letterboxes without the computer, seriously? I can't afford to take ads out in the paper for a yard sale, imagine posting all your plants in the newspaper (okay, I only have two, but I'm going to have more). Then that would mean subscribing to the paper. More papers would have to be printed, which means more trees would be cut down. Then, there would be less trees to hide the boxes in. Letterboxing wouldn't be the same.

So, thank you Ryan for keeping the database going and letting us use the website. You're helping the ecosystem at the same time.

Janet, Blooming Flowers

Quote

How would we look for letterboxes without the computer,

It would probably be more like it is for Yorkshire Tortoise. They have a published book that gets updated regularly. Worst case senerio, we each have some snail mail addresses that we could keep in touch with. Slow yes but better than nothing.

Shiloh

Quote

How would we look for letterboxes without the computer, seriously?

It can be done, though it does require a very dedicated coordinator to run it. To subscribe to the update list for our area we provide a supply of stamped addressed envelopes and 25p (50 cents) per month. Planters send their clues to the coordinator who types them up into a single bulletin and sends themout by post. Now it done on a word processor but in the early days he used a typewriter (remember those?) and photocopier.

It works, but only because we are dealing with a clearly defined area and a relatively small number of boxers. Letterboxing could only have grown like is has with the use of computers and the internet. Without them it would remain organised on a local basis with limited crossover between areas. Thanks is due to Ryan and others who have made letterboxing more widely accessible.

DM

Thank-You Ryan !!!!!

what a wonderful site you have created for all of us to enjoy. And be introduced and taught about such a wonderful basically still unknown hobby.

Quote

Without them it would remain organised on a local basis with limited crossover between areas. Thanks is due to Ryan and others who have made letterboxing more widely accessible.

Three cheers for Ryan, and others. Okay three cheers for everybody on AQ and LBNA!
This is the funnest!
K

It wouldn't be as bad if I actually knew what I was doing. =) Consequently, I'm having to teach myself how to scale up databases (and websites in general) <quote/>

Well, whatever you're doing must be working. This is one of the most efficient sites I use and has interesting enough information that I am popping in every free moment just to see what's up next.

Nice work.