Mysql – Will performance and speed improve when retrieving data from a .txt file instead of the Database

MySQL

I have a site that averages 40k page views a day and I sometimes hit around 100 concurrent users at any given time.

When a user enters the landing page of the site, the database is called to retrieve 6 random items and they are displayed on the webpage.

The database table has nearly 10k rows and it's using a fairly simplistic statement:

SELECT id, name FROM table ORDER BY RAND() LIMIT 6

Even when the landing page is cached, it still calls a .php file that still pulls 6 random items.

The SQL query generally takes around 0.0137 seconds to execute and currently isn't using an Index.

Today I changed it so that the database is called once per hour trough a cron job and the results are stored in a .txt file. The file is then read when a user hits the landing page and the contents are displayed.

I'm having difficulty measuring a speed improvement as I have no methodology to test with. I'm mainly doing this to try reduce CPU usage on the website as a whole.

I have many more live calls that I can convert over to storing in a .txt file but my question is:

Would it be faster to set up an index on the database and continually call it for each concurrent user or should I proceed with writing to a .txt file once per hour and calling from that?

Best Answer

ORDER BY RAND() LIMIT 6 requires fetching the entire table, shuffling it, then delivering 6 rows.

Performance is depending on how many rows are hit -- it is 10K per request (to get only 6 rows). Work on shrinking that.

There is no 'perfect' way to fetch 6 rows from a table, but this gives 5 pretty-good ways. See which one fits best in your app for performance improvement.

Related Question