On Fri, Dec 02, 2005 at 06:29:29PM -0500, jesse wrote:
indexes.
Perhaps I'm misunderstanding you here, but my MySQL tables already *define* indexes on the name column. What more could be done?
Have you turned on mysql's slow query logging? Sometimes it's a painful query that's not quite what you expect. Once you know what query you're talking about, "EXPLAIN ..." can be lots of help.
I haven't, and don't know how, but as I realized later on, the problem probably isn't the database itself.
Nope. But I'm pretty sure CGI::Wiki doesn't do any hard queries.
The problem is that it's selecting 700 nodes, and all the metadata for them, which is just a lot of frickin data. (The latest database dump is 1.95 meg.)
(Do you mean megs or gigs?)
It's megs. I say "it's a lot" mostly because it takes the raw data and then builds all the nodes up in memory, which is the painful part, not the database access itself.
The loading half of CPAN happens on every page. The loading 1.95MB of data and building the associated structures inside the request (attaching all the metadata and so on) only happens on the index page.
Has anyone done a FastCGI version of the openguides CGI? That can help a LOT.
Not that I'm aware of.
I'm not saying that the speed isn't related to other aspects, but at least some of this will start to be limited by the database once it gets bigger.
A couple of orders of magnitude bigger. at 2 megs of data, everything should be coming straight out of ram inside mysql.
That's assuming that the guide is the only thing using the instance of MySQL. I have 50 other sites, about 20 of which use MySQL, most of them much more frequently than the openguide, despite the fact that it's getting a couple thousand hits a day. If we move into shared hosting, you're dealing with the same situation on a larger scale: mysql is shared. The data isn't always going to come out of memory.
But the argument here is that "MySQL isn't slow loading 2 megs of data." I'm not arguing that - I'm arguing that SQLite might be. And I don't know enough about SQLite to know if that's a ridiculous claim or not, but my thought is that it might be.