1

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?

Improve this question
10
  • I'm afraid you're barking up the wrong tree. Of the 40K views, how many hit the landing page? That times 0.0137s is causing CPU usage?? An elapsed time of 550 seconds, even if all of it was CPU, is nothing to be worried about. Is your application using persistent connections? If not, connecting to the database will take more than 0.0137s.
    – Gerard H. Pille
    Commented Jun 29, 2018 at 11:28
  • Given the query, no WHERE no JOIN and an ORDER BY rand(), I don't see anything to index here.
    – sticky bit
    Commented Jun 29, 2018 at 11:28
  • @GerardH.Pille No I'm not saying it's singularly causing CPU issues but I have many other queries being fired when hitting the landing page and my thinking is that all of them together are possibly causing spikes in usage? No the connections are no persistent, opened to get the results and then closed. It just seemed to me that constantly calling it over and over may be bad? sticky: I'll be reworking the query if I go down that route but I'm trying to figure out if I should go down that route or the .txt file method.
    – GenesisBits
    Commented Jun 29, 2018 at 11:33
  • 1
    Forget the text file, MySQL can handle a much bigger load. You don't need to change a iota to your coding to start using persistent connections. I believe you just need to add a 'p' before the hostname. Then you can start to check for slow queries - not this one.
    – Gerard H. Pille
    Commented Jun 29, 2018 at 12:42
  • @GerardH.Pille Much appreciated. Persistent connections is the same as Keep-Alive correct? I'll try enabling that and see how I get on in the mean time. I have a max of 50 entry processes on my shared hosting server so I'm hoping that won't restrict it too much.
    – GenesisBits
    Commented Jun 29, 2018 at 12:52

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.