Portal style page for vb3.8

[Dave#]

Can anyone recommend an alternative portal style homepage for VB 3.8 that is not vbadvanced?

vbadvanced adds indexes to the post table which kills performance of mysql

[squidsk]

I think you have a misunderstanding of how things work. Indexes improve the performance of the database by speeding up searches of tables when the conditions of searches use columns that have been indexed. For example a query of the post table for all posts by a particular user (i.e. userid column) is much faster if the userid column is indexed than if it isn't. The cost of indexing a column (or groups of columns) is that it takes up space. The one thing that is not a cost is performance.

[Dave#]

Originally Posted by squidsk

I think you have a misunderstanding of how things work. Indexes improve the performance of the database by speeding up searches of tables when the conditions of searches use columns that have been indexed. For example a query of the post table for all posts by a particular user (i.e. userid column) is much faster if the userid column is indexed than if it isn't. The cost of indexing a column (or groups of columns) is that it takes up space. The one thing that is not a cost is performance.

I think you are the one who is mistaken.

MYSQL decides how to execute a query based on a few things, indexes is one and data size is another.

It is very possible to add an index and degrade performnce because the MYSQL query planner decides to use the wrong, more inefficent Index.

From memory, the index was on last_modified on the Post table. We have 13M posts and the MYSQL query planner did not like this at all.

Also bear in mind that every new index on the POST table means slower inserts as MYSQL needs to update extra indexes.

[squidsk]

Originally Posted by Dave#

I think you are the one who is mistaken.

MYSQL decides how to execute a query based on a few things, indexes is one and data size is another.

It is very possible to add an index and degrade performnce because the MYSQL query planner decides to use the wrong, more inefficent Index.

In other words don't have multiple indexes on the same column. Yes this is the only way to screw up the database. Regardless the index used is based on the columns in the where clause and any using/on statements. For example if you have a table with columns A and B with indexes on A, B as well as an index on AxB. If only one of the two columns are in the where it will use the singular index, if both are in the where it will use the composite index.

From memory, the index was on last_modified on the Post table. We have 13M posts and the MYSQL query planner did not like this at all.

Sorry but this makes no sense. There's no reality where searching through each and every one of your 13M posts is more efficient than using the index to only have to search a subset of those 13M posts.

Also bear in mind that every new index on the POST table means slower inserts as MYSQL needs to update extra indexes.

Yes which is an insignificant slow down in general usage of a site. The site I run, with comparable post table size of ~15M posts has over 1 million hits a month, of those the vast majority are to showthread (i.e. querying the post table) of those only about ~34K are new posts. This means that a select query that is faster is far more important than an insert query that is slower. On top of that indices in mysql are typically stored in n-ary trees (also called B-trees if you read the mysql documentation), which means the additional time on an insert is logarithmic while the extra cost to a search in linear, which for data of any size is a significant difference.

If you really don't like the index, just drop it through your myphpadmin and it won't interfere with anything else and you can still use vbadvanced.