I found a couple of sweet indexes you can put into the articles table to radically kick up the speed of your website.
add 2 index to the articles table:
triple-index on status, posteddate, article_id
double-index on status,category_id
The triple-index helps with when the code gets the 50 latest approved articles to show on the main page.
The double-index is because for some reason the code calculates the count of approved articles for each category, yet never shows them.
Testing it from a 3rd party source, i was at 4.55 seconds on the main page.
After the triple-index, down to 2.67 seconds.
After the double-index, down to under 1 second, quite often down around 0.7 seconds.
I should have written down the indexes query itself, but if you use phpMyAdmin, just create 2 new indexes through the gui and you're gold.
Now, just keep in mind, the bigger the website articles table, the more the gain. I'm at around 132,000 items in the table, and I saw a huge gain. At any stage you'll see a big jump, and it makes the code much more scaleable.
Don't forget to optimize the table every so often if you delete things on the regular basis.
At HostGator I was pushing the load averages over 30. Now it hovers under 1 most of the time. This php file will setup the indexes you need. Just upload it into the main directory on your website(same one setup.php is in), and call it from your browser(i.e. http://www.example.com/add_indexes.php) if you save it as add_indexes.php
Code:
- Code: Select all
<?php
include("setup.php");
$link = mysql_connect($dbhost, $dbuser, $dbpasswd)
or die ("Could not connect to MySQL");
mysql_select_db ($dbname)
or die ("Could not select database $dbname");
$result = mysql_query("ALTER TABLE `{$table_prefix}articles` ADD INDEX `IX_LATEST_ARTICLES` (`status`, `posteddate`, `article_id` )");
$result = mysql_query("ALTER TABLE `{$table_prefix}articles` ADD INDEX `IX_CATEGORY_COUNT` (`status`, `category_id`)");
echo "Articles table is now updated with new indexes.";
?>
Just a note, depending on the system, it can take a little bit. On most of host gator's it takes about 10 seconds, but on some slower systems it could take more than a minute. It'll echo out that it's done when it is.
Tony
Question:
My directory is relatively small - 13k articles. You guys have over 100k articles on your sites. Would small sites see a benefit too?
I bet you would - and since its a no hurt thing... you will definately see the increase in performance as your site grows. Indexing won't hurt. If you don't like it, you just delete the index. It's really a separate lookup table it creates.
