How do I add indexes to WordPress Database?

The question:

I got a message from Hostgator about my account being suspended because of the load it put on their servers. This is about the fifth time this has happened. I am on a shared server.

And they said it could be resolved by simply adding new indexes to the database or optimizing the database using other techniques. I have been using SQL queries and the “optimize” option in phpMyAdmin to optimize my database but that hasn’t helped.

I have also installed Super Cache as recommended by them but this hasn’t helped either.

Now, I want to try adding indexes to the database. How do I do this? Does anybody know how to do this please? It’s urgent.

Thanks.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

The WordPress database is already indexed. See this codex article for a detailed list of indexes per table: http://codex.wordpress.org/Database_Description

And even if it weren’t, you’d need to know what queries are being run in order to effectively add indexes. Meaning, there would be no quick fix–you’d have to learn how indexing works, figure out what queries are performing poorly, and go from there.

If you really do have slow query issues, it most likely has to do with a poorly written plugin or theme file you’re using. Try disabling all plugins and switching to the default theme, to see how it affects performance. If your site is still too taxing for your shared host (with a default theme and no plugins running), you need to move hosts.

Method 2

My thinks they gave you a very general solution to a more specific problem. WordPress uses multiple tables to query posts/pages/etc. I’m not sure if you really should be adding new indexes. I would start by putting it on maintenance mode and testing your site page by page. You need to figure out which page(s) are running very large queries, and stop them, page them, or change them. The other possibility is that you actual built a successful site, and no about of optimizations in the world are going to give you more bandwidth. You may need to dole out some more cash and get a dedicated server. If that isn’t possible, again, you need to minimize the number of queries to the database you are making.

Method 3

I would recommend you to use http://wordpress.org/extend/plugins/wp-super-cache/ plugin if your website is not very dynamic. And changes once a day let’s say. This plugin makes static files from dynamic content, so it will almost eliminates queries to database. (you can chose what’s should not be cached). It makes website super fast. [BUT make a backup before install]

Here i made a photo how to make field indexed http://s10.postimage.org/wxjn9u2uh/index.jpg BUT – NOT ALL FIELDS can be and have to be indexed. Indexed field must be specific size. And indexing needs to be done on fields you frequently making queries on.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment