It all depends on what you do. Are you going to SUM() numbers over large parts of the data or are you going to retrieve a few rows by primary ID or a set of columns with a good index?
I have dealt with InnoDB tables with up to 500 million rows. I would say that the retrieval time for a row was roughly the same as with a very small table, but as the tables grow you are likely to want to retrieve more rows. We ran statistics queries and got more and more statistics per day. That meant that the queries had to read more and more rows. However, reading single or a small number of rows by primary key or with the help of a good index is more or less as fast as ever.
The biggest problems were mostly related to backup, schema changes and other types of maintenance. MySQL's inability to perform "simple" changes – such as adding or dropping indicies – online might become a problem when a change takes 4 hours (or 2 days).
While not MySQL's fault, backing up and restoring was terrible. Simply inserting a few billion rows takes a very long time. To make sure that the backup worked, we tried restoring from backup about once a month. This required about a workday of crunching time to write all that data.
The biggest tables had about 500 million rows of statistical data. We did not split big tables but we did purge no longer needed data rather aggressively from some tables in order to save disk space. The biggest problems were not related to the query time required for finding a certain number of records, but rather that we often were looking for a lot of records when we created charts for longer time periods etc.