Huge database... queries too slow. How to improve performance? I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array of integers as text(varchar couldn't become large enough). 

A simple


  SELECT row FROM tablename LIMIT 200,000;


takes 30 seconds to complete... I need to select all 30 million rows and go through some algorithms in at most 10 seconds to be ideal.

I'm not used to managing such a large database. Is it normal for a queries to take this long? I've heard about indexing, but can't index the text data type column. 

What are some ways you suggest I can improve performance? I can't find too many ways to improve performance other than:


Indexes
More RAM
SSD
Partitioning tables(sound complicated?)


Is there anything I'm not thinking of?

I appreciate any help! :)
An index provides a path to rapidly identify the rows that match a given predicate. They help most when you need to reference a small fraction of a table at a time. Since you need all rows this is unlikely to be helpful. They also enforce a sequence. This may be helpful if your algorithm would benefit from ordering, though you do not mention it.

The DBMS copies data from disk into memory before processing the rows. So more memory helps because more data can be held in memory, with corresponding fewer IO waits. Since you need to process 280GB the last 4GB will be in memory at the end. During the next cycle that data will be evicted and fresh data read. Unless you are able to install over 280GB more memory is unlikely to help.

Partitioning is more of an archiving thing. Sharding may help, though. That would mean spreading the data across several servers. Four servers would, approximately, quarter the time to process. You'll need a custom process to stitch the answers together, though, which may not be simple.

The simple fact is that if you need to process 300M rows you need to read 300M rows. This is likely limited by storage bandwidth. An SSD will obviously help here.

However, is there a way that you wouldn't have to process all rows each time? Could you pre-calculate these values, cache intermediate results or only process deltas between queries?

Edit Are you reading all rows only to parse some values out of the text column, but you actually do not use the majority of rows in any particular query? LIKE is not going to help eliminate IO since the majority of values will be somewhere in the middle of the text. An index will not help here. The best you can achieve is to push filtering onto the DB server eliminating network time.

A full text search may help find labels within the large text column. Perhaps redefining the column as JSON and adding a secondary index would help. But really these are kludgy workarounds for a sub-optimal design. Relational databases are built to process relations i.e. tables. Structure the data properly as a table and you will have the best chance of getting good performance. In this case that means separating the key-value pairs embeded in the text column onto separate rows, one per value. An index on the "key" column (and whatever other columns are in the WHERE clause) will be a great start. Yes, the DB may become a little larger, but the software's designed for this. There are many, many systems around with terabytes of data in billions of rows. It will be OK.