• Cache your entries in perl, and use them elsewhere. You might be able to shave some cycles off by throwing your results into a hashref and keeping them around a little longer.
• Check the index efficiency on your tables. I don't know if you have looked into the CREATE INDEX documentation, but you say you're using it properly. (mySQL documentation for Create Index).
• Change your table type. You might be able to do more with a table type that most fits your sql style. There are a few to choose from, each with their own optimizations.

Cache your entries in perl, and use them elsewhere. You might be able to shave some cycles off by throwing your results into a hashref and keeping them around a little longer.

Excellent point. You might also want to consider the Cache::* modules for your caching needs. They offer fixed size cacheing, which can help you keep from running out of RAM/disk. The file based caches are very fast, and might be ideal for that less often needed data.

Also take a look at Perrin Harkin's excellent EToys article on perl.com for a great story on effective and intelligent cacheing.

Test included transactions, load testing, simultaneous requests, and more. Interesting article.

Spoiler:: MySql shone brightly...

"Overall, Oracle9i and MySQL had the best performance and scalability, with Oracle9i just very slightly ahead of MySQL for most of the run."

Plus, some tidbits and suggestions on tuning the brands they tested.

HTH

Those benchmarks look pretty dodgy to me. Whenever I see these graphs where two products follow the EXACT same line on a graph (in this case MySQL and Oracle), then I get suspicious - it basically means that the database is running at the fundamental limits of "something", be it the OS or the driver or the technology being used. That's not necessarily a bad thing, but it tells you nothing about which is faster - MySQL or Oracle. For that you need the database itself be the limiting factor, and it's obviously not in this test.

Another issue for that particular test is that it seems that the JDBC drivers used played a big role. So while the app worked well for Oracle and MySQL it wasn't well suited for Sybase (Sybase isn't very good at handling cursors, because in general you don't need them...)

Michael

IMO if you're not facing perf issues (altho' you i think you infer that with the comment about placeholders), things tend to dive faster into "kaos" the more complex it becomes.

For example if you're making heaps of calls because the information in your db is poorly normalised that would be messy in my book.

Another example, if you're making heaps of calls because youre trying to fit too much information on on page. That is a messy application design. In this case, I would tend to break the site into categories and have each category in seperate sections to dilute the content a touch.

If your application is getting too big for one developer, and you only have budget for one you could heading into "messy" land. I say this because once you have a system so big, bits of it will be forgotten, and when something changes, something breaks, it then takes time (and cashola) to track down the problems which can further compound other issues you may be facing such as time to market, and lead to resource contention.

If youre redesigning your site, dont forget most users dont like things more than about 3 clicks away.

Cut off? purely arbitraty depending on your application design, info system back end, hardware, performance, et al. Take a look at sites that have lots of aggregated content, (such as yahoo) and copy the design if you need to.

In general the fewer database requests the better, but you should not necessarily focus on the number of requests but rather on the time it takes for each of those requests to complete.

Michael

It's not 15, but Perlmonks has between 4 and 10 selects per page (depending on the number of nodelets you have). Huh ...

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

Some table types penalize you less if you are doing selects and inserts at the same time.

You might want to see if you can apply GROUP BY to reduce the number of selects you are doing.. The presorted output is much faster to access within Perl. I got 20x-30x efficiency improvement for a spreadsheet report generator.

If you can make more complex data objects and maybe serialize you might be able to get away with a just a couple of statements. Perhaps if you post your data structure and what you need to do you could get more help.

In MySQL, in my current project, it was the Group by's that were killing me. It's much faster to just slurp all of that information into a hash ref and do the grouping in Perl. The same goes for SUM and COUNT calls. If you are doing more than one or two of these calls, you pretty much NEED to pull that data into Perl and do the additions there.

oakbox