Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others.
Take, for example, this gem:
> Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results.
This describes just about every standard page-with-comments schema ever devised.
It struck me with particular force because perhaps I've been unfairly blaming Wordpress for the failings of MySQL, vis-a-vis the atrocious performance of the Recent Comments Widget.
I see horrible join performance on tables with about half a million rows, which is chicken feed. The query cache "solves" my problem, but it's amazing that it should be necessary for very trivial joins between 2 or 3 tables.
Is it MySQLs fault that wordpress asks it to do something that's hard?
LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it
I know WordPress quite well. Most of these queries can be very easily executed from indexes, pulling from the tables only the data that will be output. It's just that mysql chooses to do it the dumb way.
> LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away
In fairness, that often depends upon the intelligence of the planner. A lot of sort-limit-offset queries can be reasonably easy and not generate too many rows if the planner works correctly and you have the right indexes.
edit: Assuming you're mostly picking up early pages as opposed to getting late ones, anyway - which is usually the case for, say, a comment system.
Take, for example, this gem:
> Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results.
This describes just about every standard page-with-comments schema ever devised.
It struck me with particular force because perhaps I've been unfairly blaming Wordpress for the failings of MySQL, vis-a-vis the atrocious performance of the Recent Comments Widget.
I see horrible join performance on tables with about half a million rows, which is chicken feed. The query cache "solves" my problem, but it's amazing that it should be necessary for very trivial joins between 2 or 3 tables.