Quite.
Up until about three years ago my mail archive was running on an 866MHz P3 box with 512MB RAM, so not totally out of line with an RPi. Ran on Fedora rather than Debian.
Software: the archive is written in Java and uses a JDBC to talk to its PostgreSQL database.
Size: at that time the archive was around 2Gb and contained about 50,000 messages.
Performance: whole-database searches on body text took about 25 seconds. Body text searches on the last two year's messages took 5 seconds.
I did almost no Postgres tuning to get that performance, but OTOH I have been a DBA in the past and do know something about schema design and appropriate use of indexes and constraints to support entity relationships.
Current: exactly the same software, though with later Fedora, Java and Postgres versions, is now running on a 3 GHz dual core Athlon rig with
4GB RAM. The database is now around 420,000 messages. The whole DB scan takes 10 seconds or (2-3 secs for a search limited to the last two years.To the OP: you *do* need to take the time to understand any RDBMS. The software must be tweaked to suit the hardware its running on. In the case of MySQL your choice or whether to use INNODB or not will be crucial to performance.
Last but not least, many DB schemas are poorly designed by people who should know better and may need modifications to get adequate performance. Watchpoints:
- is the schema fully normalised to 3rd normal form?
- is every prime key supported by a unique index?
- are all relationships supported by suitable constraints and indexes?
If not, *fix the schema* before doing anything else. If it is OK, use the database's tuning facilities (the EXPLAIN verb or equivalent) to see how the RDBMS is executing any noticeably slow queries and take the steps needed to fix the query performance - usually by adding missing indexes or, less rarely, by rewriting/simplifying over-complex queries or stored procedures.
Yes, you *do* need to know this stuff if you're going to use an RDBMS for any non-trivial application. And, if you're using it to store long term data, you need to know how to handle backups and to migrate the data to a later version of the RDBMS.