[swiper]

I noticed that in the database there is no index on the link_status column in the pligg_links table. Almost every page load on the entire site includes 2 queries of this column, so adding an index on this column should provide a noticable improvement. I'm just launching my site now so I can't say how much, but it will definitely help. Once you have 1000+ stories in your Pligg site, having no index on this column has to be a killer.

[brandondrury]

This makes total sense. While I've done my share of php, I hadn't quite gotten into MySQL performance tweaks yet, but this seems consistent with everthing I've read.

When some more knowledgable dudes confirm it, I'll write a blog on PliggPower.com.

Thanks!

Brandon

[nothingman]

"Almost every page load on the entire site includes 2 queries of this column, so adding an index on this column should provide a noticable improvement."
Wow! so how does one go about solving this :-/

[AshDigg]

I'm going to work on the queries this weekend. Looking at the query.log there are a couple things that need to be worked on.

[Atma]

It's a give and take scenario. It speeds up select queries, but if you're doing an update or insert, not only are you updating the data, but the database has to update the indexes as well. For all intents and purposes, this is generally a good idea. You figure for every record update for a semi busy pligg site, you're going to do 100-500 reads, so the overhead trade off on record updates is worth it, IMO.

I'm going to implement this myself shortly.

EDIT: I personally didn't see a major improvement, but it was noted. If somebody has time to do speed tests, I'd be interested.

[dollars5]

Originally Posted by Atma

It's a give and take scenario. It speeds up select queries, but if you're doing an update or insert, not only are you updating the data, but the database has to update the indexes as well. For all intents and purposes, this is generally a good idea. You figure for every record update for a semi busy pligg site, you're going to do 100-500 reads, so the overhead trade off on record updates is worth it, IMO.

I'm going to implement this myself shortly.

EDIT: I personally didn't see a major improvement, but it was noted. If somebody has time to do speed tests, I'd be interested.

I was about to tell - I remember from my database classes at grad school, indexes are very optimal when you have more read than the number of inserts and updates, so if you take a simple Pligg site - it would normally have a huge pageview than less people actually voting, commenting and editing content - so I am sure indexes are gonna help us.

[swiper]

nothingman - To add the index just issue the following in mysql (via your servers admin panel, SSH, etc):

alter table pligg_links add index(link_status);

[brandondrury]

Okay, so what's the verdict? Should everyone add an index or is this a case by case basis?

Brandon

[dollars5]

Please wait for a few days - Ash would be providing a significant update on the indexes and database optimizations - he has mentioned this at the top of this thread.

[nothingman]

Thanks swiper. Anything else I need to do? I have about 1800+ posts on my site