Hi there!

I run www.omlazeni.cz

The database size is 300MB, we have 650 000 posts, 12 500 topics and 17 000 registered users.

We have just moved to VPS and I think of dedicated server so the computing power is not or will not be problem, also we have plenty of disk space available.

Still, I realized it is high time we thought about some database performace optimizing.

I dont understand databases, but I have read something so these are my thoughts:

- Index over post_text might improve search
- Multiple indexes confuse me. I dont know why they are there. Let me quote phpMyAdmin:

"The following indexes appear to be equal and one of them should be removed: topic_last_post_id, topic_last_post_id_2"


I seek help in two forms:

1. Free tips of how layman might make some quick fixes. (Like "Yes, remove the multiple indexes, nothing bad can happen and it will improve performance a bit.")
2. Mayby paid help from team (Paul?), if there is some real room for database performance improvement.

I forgot to mention that the performance for normal users is quite OK, but for admin and mods it is sometimes painstakingly slow - when we move or delete topics or posts...

Multiple indexes were dropped in one of the earlier miniBB releases, you may follow updates history to get a precise version. They are left by mistake and one of them should be removed. Though this is related only to topic_id or post_id indexes as I remember. topic_last_post_id_2 doesn't belong to miniBB. I don't know where it comes from.

The command for dropping an index is quite simple, and it should be run from the SQL command window:

Regarding post_text index - you probably mean FULLTEXT index. It has no effect with miniBB. FULLTEXT is still a very experimental index on mySQL side, and it probably will work only with English encoding on full capacity. I never checked it with non-English texts. Its algorithm also pretty differs from what miniBB search provides.

We agree that on large databases miniBB search may be not sufficient at the moment, but it appears more limitation of mySQL and how it allows to search within a database by a free text. If you want full power on text search, it's better to use some external engine like Google's search or something similar. Searching within tons of text is not the task for a small software.

You can't improve anything with moving or deleting of posts. When you delete just one message, there are at least 5 other operations like UPDATE involved. UPDATE or INSERT operations are most heavy for mySQL. In you feel mySQL works slow, you need a server dedicated for mySQL only.

Thank you Paul!

Did you run this all on a shared host? If so, I must say the host was very good ;)

I will tell them...