Date: 03/25/05 (MySQL Communtiy)    Keywords: php, mysql, browser, database, sql

I'm trying to create a PHP browser-based chat room application. It will include Savant, JPSpan, and a custom MySQL wrapper class. The server is running PHP 4.3.10 and MySQL 4.0.18-standard. Now that the background information is out of the way, to the real issue at hand...

I obviously want the application to be able to support as many users as the server will allow. That said, the application must be as efficient as possible in terms of the database design and implementation. The database will be relational in design. There will be tables for rooms, users, and posts made by users to rooms. The application will also include a feature to allow users to search posts. My plan at the moment is to use a HEAP table as a buffer for current posts, given that they provide a speed advantage because they exist in memory and use hash indexing. On some sort of timed interval, posts would be moved from the HEAP table to a separate permanent table for archived posts.

However, this approach would require that posts be archived regularly over the course of the day. The more frequently posts are archived, the smaller the number of posts that could potentially be lost due to a server crash, power less, etc. The HEAP table really only needs to hold posts long enough for them to propagate to users. I'm not certain what frequency would be best.

Also, because HEAP tables don't support the TEXT field type, the HEAP table would initially need a VARCHAR(255) field for the post body. The application would have to check the length of posts when users submit them and then ALTER the table to add additional VARCHAR(255) fields, split the post body up to store it across those fields, and then concatenate the value of those fields to display the post. Since displaying each post would require getting all fields in the table anyway, it should be fairly easy for the application to figure out with each display how many fields it needs to concatenate to display the post body.

Any input? Any alternative approaches? I probably neglected to mention some tenant of my application requirements, so if you have questions, please ask, and I'll most certainly answer them. I think I've covered most of the bases, though. HEAP tables are a new concept to me, so I'm mostly lingering in the world of hypotheses before I actually try to make this work.

Source: http://www.livejournal.com/community/mysql/52183.html