Search code examples
phpmysqltextvarchar

Mysql - most optimal way (performance and scalability) to store and retrieve multiple lines of text

I would like to store large amounts of lines of text into mysql. I've researched this, but i wasn't able to find an answer. Some of the answers I found include:

  1. Create a varchar column and store all the text them there.

  2. Read and write to a text file. That text file is located on a disk, and mysql keeps the path of that text file.

  3. Read and write to a php file using an array. That php file is located on a disk, and mysql keeps the path of that php file.

But I'm not sure what are the pros and cons of each.

For my specific purpose, I would like to constantly add lines. This will eventually create a LARGE number of lines.

Ex.

Line 1: Hello World...........
Line 2: Hello World...........
Line 3: Hello World...........
Line 4: Hello World...........
...
Line 50,450: Hello World......

What would be the most optimal solution - performance wise, if I were using php to read and write lines?

I would like to do something like:

Read and display the first ten lines. When the user presses the "more" button, the next ten lines of text would be read and displayed.

Solution

  • You might be looking for pagination in php using a mysql query like SELECT * FROM table_name LIMIT $start, $limit.

    Here is an example, this uses mysql_query, but you can substitute that with PDO. You can also use redis, if you want to cache your results, so that you don't have to query the database every-time.