MySQL 4.0 rocks my world as well.

I don't want to copy Jeremy Zawodny with his post about how much MySQL 4.0 rocks but I had to metion some of my experiences with it.The main thing about MySQL 4.0 that is changing my world is how much better it handles maintenance of FULLTEXT indexes.PittsburghLIVE's story database has 2 full text indexes on it's story table. As the story table grew to over 60,000 records, we started having a problem with repairing the table. If the tables got corrupted it would take 6-8 hours to repair the table. I have a 9 or 10 hour window where I can lock the table and repair it, but the issue is that I can't lock the table until the editors are done and the database is very SLOW in the meantime.My solution with MySQL 3.23 was to have two story tables. One requirement was to be able to do updates while we are doing that 8 hour repair. The solution I came up with was to have two story tables, one that contained all the stories and one that only contains newer stories. When stories are added they are inserted into the small table. There is then a cron script that takes stories that are only in the small table and replicate them to the full table, and to delete stories over 60 days old from the small table.I then had to write some intelligence into the front end that it would figure out which table it should use to find the desired story. It turned out that this solution had worked very well, except for a few bugs, one of which was where a query had to look in the big table but the newest stories haven't been replicated to it yet.Our big table is now 125,000 records the data size is 518MB and the indexes are 287MB. MySQL 4.0 take 10 minutes to repair the table. With the repair time on only 10 minutes, it allows us to get rid of the hack that splits the stories across the 2 tables. So MySQL rocks my world also, and I haven't even started playing with the cached queries yet. I'm still trying to figure out how exactly it will help our setup. We are currently using the PEAR DB_Cache classes for caching witch works rather well. There are two main advantages of MySQL caching over the PEAR cache, It's automatic and there isn't any stale caches. PEAR caches take some setup in development, and run solely on their TTL's. Now that we have PEAR cache setup it's virtually automatic, so the only downside is that there can be stale caches. An upside however is that the data is cached on the web server so the data doesn't need to be transferred from the DB server every time.We have our TTL's set really short at only 5 minutes. but with our site this still gives us a tremendous savings in queries. There shouldn't be too much overhead by enabling query caching on the server if it's tuned correctly so I may enable it, It will catch those queries that come in once every 5 minutes.Well that's my preliminary experiences of MySQL 4. and my first attempt at an informational blog entry, I hope somebody finds it interesting or helpful.