Oracle are obliged to fix security bugs and updates for MySQL 5.1 until the Premium support period has ended - remember that there are paying customers out there using the product in production.
See page 13 of this document for support end dates by MySQL version.
MySQL 5.5 is more than ready for production use & I'd recommend using that for any new projects going forward.
Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.
An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.
For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.
What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.
From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.
Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.
Best Answer
MySQL's sql_mode setting allows you to enforce or relax ANSI standards for SQL processing.
In your case, MySQL will regress in behavior and results. In what sense ? MySQL 5.1 version features will stay, but how the SQL is processing changes. Here is proof:
MySQL 5.0 first introduced Stored Procedures to the MySQL realm.
MySQL 4.x never had Stored Procedures.
Now, let's take a look at the physical home of MySQL 5.x Stored Procedures,
mysql.proc:Please note column 15 :
sql_modeLook at those modes. Some say
Based on whatever RDBMS you worked with before, you can make the SQL inside the Stored Procedure get up and do any song and dance you like (in other words, you can make the execution of the SQL as strict or as permissive as you wish based on the protocols you chose).
This would allow you to port any code you wrote in MySQL 4.x into a MySQL 5.x Stored Procedure and expect the results in the same order as if you ran it in MySQL 4.x.
I once wrote an answer to a post about relying on undocumented MySQL behavior (What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?). In that post, I wrote a comment that said:
This gives you time to reflect on how to properly plan the migration of SQL code from MySQL 3.x, MySQL 4.x, or other RDBMS products into MySQL 5.x. It also warns you that result sets from SQL can radically change if you tamper with the sql_mode, so use it with extreme caution.
I hope this answers your question !!!