coder
coder

Reputation: 301

Wordpress Database stucture

I want to make a ajax post filter. Filtering on wordpress post meta (custom-fields).

My posts have post-meta like actors, genre, duration, ect..

If I make a script using the meta-data from wordpress I expect performance issues. Therefore I want to build a custom database-table containing the post-id, and then a copy of the post-meta.

My structure would be something like this:

post-id actors genre duration

Is this the most efficient way, or should I make separated tables for every post-meta?

Like this:

table 1: post-id actors

table 2: post-id genre

table 3 post-id duration

It needs to be optimized for both a large database, and high volume traffic.

Also I am open to better ideas, than the ones above.

Upvotes: 0

Views: 151

Answers (1)

bokan
bokan

Reputation: 3692

Wordpress looks terribly designed, but it does the job and do it well. Strangely it's much more efficient that what we can think by studying at its design.

If you want to use another table you will have to sync its content with the wp database on post updates. May not be easy.

I would take this problem in either one of this directions :

1 do it wp style and optimise

Do your database with custom post type. Create a page called ajax-actors in admin, create a page-ajax-actor.php file that handle the query and send raw results (no header, no footer). Call www.yoursite.com/ajax-actor with ajax. This will be "slow", so you have to optimize it. Make sure you have apc running with enougth ram allocated to it. Use wordpress supercache or similar plugin / Use a reverse proxy

2 do it using lightweight database

Use wordpress for your editorial content and create a second lightweight database from scratch. This way you can optimize your database for its specific content.

If you are starting a new website, do it using the 1st solution. It will be faster and you'll get your site online sooner. Then you can use the revenue to optimize it or even do a complete reshape.

Should the values of actors be: Brad Pitt or a integer: 232 related to his name?

You should use one table per entity type. Primary key must be an integer. Then you reference this primary key from other tables. Example : Table actors id : integer / primary key name : varchar / with index

The index on name slows insert but will speedup dramaticaly the search using actor names. In the movies table you reference the actors by id. Actually, since they are several actors per movie, you'll need a table like this : Actors_Movie actor_id : integer movie_id : integer role : varchar (what's the name of the character of this actor in this movie)

hope this helps.

Upvotes: 1

Related Questions