Hello

Thank you for your follow-up. :)

I'm using phpMyAdmin to optimize all of the database tables every evening.

I assume you do it in the tab "operations"->"optimize table"[1]. And, as an additional assumption, you do it for every table step by step?

Citation from the MySQL manual:

Use OPTIMIZE TABLE in these cases, depending on the type of table:

• After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.
  
• After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

Ok, it's a defragmentation of the physical database file(s) in itself in case of many changes (many postings) and the presence of (many) columns in the database tables with variable length (VARCHAR, VARBINARY, BLOB, or TEXT columns). In our case it's the nature of postings in a forum to be of a variable length. Also the titles of postings, names of posters and other additional informations are intentionally of different (string) lenght for most of the entries.

Maybe the length of some of the data fields can be optimised. Examples: the unique id for a posting (field uniqid) has a lenght of 255 chars but the value has in my test installation only a length 13 chars for every single entry. The field ip has a length of 128 while an IP(v4) can have a maximal length of 15 chars(!) [2]. There are other fields for user data in the entries table with a length of 255 chars. Not in every case this length is suitable.

Especially in case of a posting from a registered user most of these fields stay empty in the entries table. But the fields have to be present because of the ability to post without a registration.

It may be a solution to split the two tables (entries and user data for registered users) into three tables, one for the postings, one for the user data of registered users (both are existing) and a third table for the user data of not registered users with a link to the posting.

If only registered users are allowed to post entries the third table stays empty. If not registered users can post in a forum, the third table replaces the user related fields in the entries table and that only, if the poster is not registered! There is, from a technical view, no change for registered users with this structural change. On the other hand the structure is much more clear and the database size will decrease a bit because there are only entries for user data if they are needed.

This needs a fundametal discussion about the database structure. Alex as the maintainer of the project has to be involved.

If I don't optimize even just one evening, the site is noticeably slower. What I mean by that is once I enter the URL into Chrome's address bar, nothing loads for a few seconds (white screen but with spinning icon), then suddenly the whole site loads pretty quickly. The longer I wait between optimizing the database, the longer this initial delay. After a week, it can take 10 to 15 seconds to load, sometimes more, depending on activity. This week there is less traffic than usual because of the holidays, so the effects wouldn't likely be as severe.

I get similar complaints from my users who experience the exact same thing. They get the "waiting for surveyorconnect.com" message in their browser's status bar for several seconds. Some take longer, but I'm sure that is all dependent on each user's internet connection and how good it is at the time.

Your description of the behaviour is plausible. Thank you for your explanations.

[1] The names for tab and link are reverse translated (by me) from my german phpMyAdmin installation.
[2] When an IP is stored as an integer it would have a length of four bytes. But this needs many changes in the code (especially many many database queries).

Tschö, Auge

[edit]PS: If you optimise the tables from the "structure" tab you'll see a note about ... err ... (maybe) backlog (in german it's called "Überhang", someone with a better translation is invited to correct mine). Can you please tell us the typical values for the affected tables? Thank you.[/edit]