Long, dark night of “the loop”…

Here is a line of thought. The 10 most recent posts are not necessarily stored at the top of the pile in mysql.

Here is a line of thought. The 10 most recent posts are not necessarily stored at the top of the pile in mysql.

I don’t know how WP’s records are stored, but — just thinking out loud, here — wouldn’t you need your data accessible in date order if your application (i.e., a blog) is going to display records in date order?

I don’t know anything about MySQL but I know other platforms let you create indexes on different fields, so that you get fast access no matter what physical order the records are in. I’m thinking MySQL — being a powerhouse DBMS — must support that kind of thing.

Well, as far as I can tell, WordPress is better than most tools when it comes to performance. You might want to upgrade to the very latest of WP, and to get the WP-Cache plugin. Both will significantly increase the performance of your site.

Also, check your MySQL database. [Long pause] I lack words to tell you how much I hate MySQL, its pathetic features, and its pathetic performance. I hate it! I hate it! I hate it! Ahem… Make sure it is properly indexed and optimized.

In particular, I recall there is an optimize table feature somewhere in phpMyAdmin. And you’ll want to index fields that you access (or use to order by) the most.

Accessible by date order does not mean that they are added to the table top. More likely the bottom I would think. This has to be a mysql tweaking thing as D de B suggests.

Thanks, Denis and Root.

I’m trying to take a long view of this issue. The latest version of WP might improve performance a bit (I’m still running 1.5.1) but I’m guessing that that improvement will be quickly offset by the increasing size of the database.

I will look at the WP-Cache plugin. The way I understand caching, though, you lose the ability to display random data (e.g., quotes and images) since the page is built and then cached. Repeat visits during the cache interval will mean you’d see the same “random” components. Or am I wrong about that?

Yes, there is an optimize table feature in phpMyAdmin. I’ve never used it because I don’t really understand it. But I’ll investigate.

Your comment about wanting to index the fields used most surprises me — and maybe that’s just because I don’t understand how WP interacts with MySQL. I’d have thought that WP would “instruct” MySQL to create the indexes it needs — not the user!

Obviously, I have a lot to learn about this. Thanks again.

It doesn’t matter how well indexed a table is, the more data that is in it the slower it will be. That’s just a fact of life in databases. You might be able to tune things up in MySQL, and the WP people might be able to tune their SQL, but MySQL isn’t the fastest RDBMS in the world by any stretch.

It doesn’t matter how well indexed a table is, the more data that is in it the slower it will be.

When you say “more data”, are you talking about the size of the database or the number of records?

Each post on my site is lightweight: a Title, Categories, Source (a small custom field), and the Body is a few sentences of text — no links other than the Title, no images, no comments, no trackbacks. Compared to most blogs, an entry on mine is tiny. I’m guessing that the size of the database is relatively small even though the number of records is high.

Size means number of rows, number of columns, and volume of data, they all play a part. Number of rows is probably the most important though.

Well, a “real” DB would optimize stored procedures along the lines of what you say, and more. But MySQL is everything but a real DB. In MySQL you get, er… tables and records and er… lemme see… er… and that’s about it. Even the SELECT statement doesn’t work properly.

That to say, you’ll want to create a couple of indexes. As a rule, anything that you query often (e.g. a date field, etc.), do not hesitate to index it. When using a real DB, indexing is hardly useful until you join tables with hundreds of thousands of records. MySQL, however, will take forever to make even the simplest join on 10,000 records if there is no proper index.

Also note that the optimizer of MySQL is rotten. If you’re managing a lot of records, you might want to insert a AND 1 = 0 in the loop’s where clause via the relevant plugin hook, so as to rewrite the query entirely via the the_posts hook. Indeed, and in spite of php’s reputably poor performance, I found that it was sometimes worthwhile to work around MySQL’s ludicrous performance by querying a couple of bits myself, in order to “manually” optimize the query.

Thanks, lambic. Not good news, but what I needed to know.

Thanks, Denis. I honestly had never heard MySQL criticised on those points before. Do you have any thoughts about whether PostgreSQL or SQLite are better performers for a large database?

Postgres is a better RDBMS, but it is less supported.

PGSQL is a bit better, for the little I played with it. I’ve never tried SQLite. But, it won’t make much difference if you’re going to stick to open source software. Most open source devs are people who have no clue of how to use stored procedures, triggers or SQL functions, so you’re essentially comparing simple select, insert, update and delete statements that devs bind together using php. Whereas performance-wise, you should be comparing how the DB handles a bunch of immensily complex stored procedures.

Umm, why the obsession with stored procedures? For a simple application like a blog, straight-forward queries should be fine, and in many cases more efficient than using procedures.

Even in Oracle I wouldn’t use stored procedures to return my data in a blog app.

For one thing, because a stored procedure is compiled, so will run faster. Also, each time the stored procedure is run, a gen alg in the SQL engine investigates further optimization until it finds the optimal sequence that leads to the result set.