Wordpress creates eleven tables: wp-commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms, wp_usermeta, and wp_users.
MySQL Workbench generated an EER Model you can see nearby. The first thing that caught me eye was the use of unsigned BIGINTs in every table as keys. Usually you want to use the smallest data type you can for keys. The smaller the key, usually the fast the system can evaluate it. But then maybe there are blog out there that need 18,446,744,073,709,551,615 posts or authors or links.Hmm, can I start a side business making Wordpress faster by converting all those BIGINTs for those who only to expect 4,294,967,295 posts??
Since I am using MySQl 5.6, the table formats defaulted to InnoDB. Some plugins for Wordpress require full-text search indexes which is now available in the 5.6 preview release.
6 comments:
There doesn't seem to be any appropriate way of getting in contact with MySQL devs, I'm hoping you're a reasonable contact. It's been 6 years and a 'trivial' bug to do with miliseconds is still not fixed in MySQL. Other forks like MariaDB, Drizzle etc have fixes for it in place already: http://bugs.mysql.com/bug.php?id=8523
People are even offering to put money in to support it being fixed. It's almost like MySQL devs are just sweeping that one under the carpet and pretending they didn't notice.
Your posting struck a chord with me. This is one of the things on my to-do list that never seems to quite make it to the top. It's a very chicken and egg thing, but I frequently find myself wanting to refactor some of the "old baggage" from a DBA perspective, with the php apps that have their roots in MySQL 3.23 or 4.x.
The problem I always hit is the app devs always resist, because they feel a need to code to the lowest common denominator (due to people using shared hosting etc), which is frustrating because resisting progress, is the antithesis to our industry.
Hosting companies will never upgrade their MySQL offerings if the customers don't demand it; and the customers will never demand it, as long as apps are coded to MySQL 3.23 standards and never evolve to their full potential by using the DB to full effect. Why cascade on delete in the PHP code and risk orphan records, when you can do it in DB more quickly, easily and safely in the DB?
I've noticed a few projects starting to push the bounds, Drupal, Magento etc, but sadly they are the minority. Oh well, it's on the list.
GeekTony, working for a hosting company, I can assure you that we love nothing but to upgrade customer's MySQL servers. From a support/maintenance point of view, there's nothing as frustrating as old MySQL instances with its limited performance, backup strategies and default values etc.
I would agree with your point, but looking at it from another point of view, maybe it's a good thing? It reins developers in a bit. The flip side of the coin you describe is a huge jungle of various versions of various software all trying to somehow work together, work around each others bugs, speak the same protocols and so on.
Slow and natural growth is maybe the best middle ground, the lesser of all evils?
This blog entry has consumed more resources than INT/BIGINT difference on all the wordpress instances...
Many legacy open source applications have kept backward compatibility, which I think is a mistake for the fast paced technology world.
Several more immediate things struck me with using WordPress. If you want convert tables to InnoDB (i.e. have a crash save DB), then you need look at all indexes. The wp.posts.type_status_date is an example of an optimization in InnoDB of removing the PK from the index.
Uniqueness is also managed by the code, for example wp_users.user_login (which should be a unique key) is not.
Finally, I like consistency and standards. While this does not affect performance, consistency in naming standards for columns, especially primary keys is a long overdue need for updating.
Dave, what happens if I want to use WordPress on a cloud database other than MySQL? Assuming I want to use WordPress, are there any significant dis/advantages to sticking to MySQL?
Post a Comment