I'll give you what I've been playing with at work lately.
We've been working on a logging setup for apache called mod_log_sql. What happens is, rather than write a line to a flatfile with all that stuff, it writes it to a mysql table. We also get a lot of traffic... so dealing with the size of that table and what mysql can handle has been my charge for a few days.
The table in question consists of a few varchar's (5,40,50,50,255,255,255) two unsigned INTs and an unsigned SMALLINT. Which if I can remember off the top of my head is something like 920bytes a record, so lets say 1K to make it easy. This table has no index's.
At about the 200,000 record mark queries started to break what most of us are used to with mysql, somtimes taking a full second or two to complete. Around the time we hit 650,000 records a query with two "LIKE '%string%' clauses in it was taking 12 to 15 seconds to complete. I'm not complaining, it does what I need it to do, but it certianly is not fast enough to embed in a web application.
BTW: this is on a P3 1.13Ghz machine with 256Mb of pc133. The only other load on the system is the stream of inserts putting the records into the table.
Now in my case I'm hosed for a few reasons.
1.) "LIKE '%blah%'" searches are a lot of overhead. Fortunatly I'm doing them on relativly short varchars, and not large packed TEXT columns.
2.) I have no indexes. They could probably drastically speed up some of that searching at the cost of INSERT time and disk space. Unfortunatly mod_log_sql will bog down the apache process very badly if it doesn't get fairly imediate satisfaction from the sql server, so I can't have indexes.
3.) MySQL's locking granularity is by page not by record (on MyISAM). This means as INSERTS are constantly adding to the last "page" of the table, my SELECT query is having trouble getting its turn to read the table.
Simiarly on another application I'm working on we have 8 tables, and nearly every query joins 2 - 4 of them. They all complete in under a quarter of a second though as any given table has yet to break 100 records (this is on an almost completely un-loaded 700mhz xeon).
So enough about my stuff, for your task you've got to address a few of the basic considerations.
- concurrent access. Will 2 - 5 office workers being accessing this frequently throughout the day, or will one lady load it 2 or three times a day.
- is your insert-to-select ration extremely low (as is usually true) and can you therefore afford many indexes
- will your application often involve multi-table joins
- will your application often involve fulltext or 'like' searches.
Even if you will have 5 concurrent users of 6 or 7 tables of ~500 records with no index's using mostly 2-table joins with one or two fuzzy comparison searches you'll probably be fine as long as you're not on a already near-overload shared-server. However if you start joining against or searchign through that 3-million record table you may find things a bit sluggish if your queries are complex at all.
Interestingly enough, PHP will probably be faster than you need it to. Most of the 'work' is performed by mysql in these apps. Unless you're using this data to dynamically generate images or pdf's or anything. Or if you're not using joins properly in sql and you replicate that with array searches and ugly nested looping in php.
Now ask yourself, is there a logical point in this application where some page does not have to be generated on the fly each time a user loads it. For instance, with my task I'm going to write some daily and montly cron'd perl scripts that do the selects I need and create the html files to show the statistics.
Take this, your contacts database is stored in mysql. But everytime someone adds a record the php script fork's off a perl script that extracts the data, builds a static html file, and replaces the old one sorted say alphabetical so organized into a few pages. At that point a user can go to the "M - S" page for "robinson" and use their browsers built in find feature after they have downloaded the static html page.
Anyway, this might not work for you at all, I just find that more web developers should be asking themselves things like "does my blog need to pull from the database every single time a user visits... or once each time i update it".
