1

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.

Improve this question
1
  • To optimize for queries, you need to first know what queries are being run. Start by installing the Debug Bar plugin, loading up a problematic page, then clicking the Debug link at the top right of the Admin bar. You will also need to add define('SAVEQUERIES', true); to your wp-config.php file. This will let you see what queries are occurring and how long they are taking to run.
    – Otto
    Commented May 21, 2012 at 21:11

3 Answers 3

Reset to default
6

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.

Improve this answer
7
  • 1
    I think he knows that it's already indexed, but it's not indexed good enough. Let's say meta_value is not indexed and almost all websites with custom search uses search in those fields which is not indexed so it's slow. So if you trying to say that WP done good job on that - i disagree. Of course we can not see what fields this guy is searching it would help us.
    – Tommixoft
    Commented May 21, 2012 at 19:14
  • 1
    From the sound of his question, I don't think he does know that. Sounds like he's hoping there's some kind of "add indexes" command you can run to improve performance, and I'm trying to explain that it's more complicated than that. He needs to understand his queries before deciding whether he can improve their performance or not with additional indexes. It doesn't sound like he's in a position to do that, since he's literally asking how to add indexes, not WHICH indexes to add or why. Regardless, yes WordPress is indexed perfectly well for an install using the default theme with no plugins.
    – MathSmath
    Commented May 21, 2012 at 19:56
  • Hi Guys, Thanks for the replies. I knew Wordpress was already indexed because my host said, "Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy." "Additional indexes" was the phrase they used. Anyway, it is actually the post-new.php (for posts) that caused the high load problem. Whenever I click on "Add New", it loads forever (with a blank, white screen) and then I have to close the tab and try again until it actually opens the page. How do I fix this with more indexes? Thanks.
    – Sosthenes Kwame Boame
    Commented May 21, 2012 at 20:41
  • 2
    @KwameBoame You don't know that you CAN fix it with more indexes. Indexing is not the solution to all MySQL-related issues, and if you don't know what your exact issue is, you don't know that additional indexes will fix it. It may have nothing to do with MySQL at all--it may be a bad loop in a plugin that's causing a perfectly good query to run too many times. Who knows. If your host is sure that it's an issue with one of your queries, ask them to turn "slow query logging" on, and provide you the log. In the meantime, I'd suggest again disabling all plugins to see if that resolves the issue.
    – MathSmath
    Commented May 21, 2012 at 22:17
  • 2
    @MathSmath thanks for your suggestion. It worked. The Super Cache plugin was the culprit. It made post-new.php make several hundreds of queries until it timed out. Now, I've got to look for a better caching plugin. Thanks for your help, my friend.
    – Sosthenes Kwame Boame
    Commented May 23, 2012 at 7:50
1

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.

Improve this answer
2
  • Understood, and I agree to a point. He should still be able to run his site without causing a strain on the server; but again, you could literally have too many people coming to your site (which is a good thing) and you need to upgrade your economy package. Wordpress's indexing is good enough to run a fairly large site with no issues; but again that depends on the plugins that are querying and the theme. Also, if you have 100 plugins installed and active, that isn't going to help. The easy route: upgrade from you economy hosting. The hard route; diactivate plugins and optimize queries
    – Aaron Bell
    Commented May 21, 2012 at 19:23
  • @AaronBell, Hi, please see my reply above (to Tommix and Mathsmath) and see if there is a way to get my site fixed. Thanks.
    – Sosthenes Kwame Boame
    Commented May 21, 2012 at 20:45
0

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.

Improve this answer
1
  • 1
    The OP says they've already tried using Super Cache and it didn't resolve the issue.
    – mrwweb
    Commented May 21, 2012 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.