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?