The EXPLAIN syntax is definitely what you need to be looking at and sharing (also, table format and # rows is useful... but a quick look at your query, it looks like it'd invariably be CPU intensive as you're:
- Evaluating 5.3 million records to find the unique values for r1
- Generating 5.3 million floating point random numbers with RAND()
- SELECT MAX(id) FROM visits) hopefully has an index on `id` or it will be slow
The 2nd query also has the additional REGEXP.
To start with try changing
And see if there's any improvement. See the manual page [php.net]
If the ORDER BY is not necessary try removing it.
Experiment to see whether any of your subqueries/functions are making the whole thing slower.
Sometimes it's also worth creating temporary tables to make these larger queries run more smoothly.
EXPLAIN syntax and output should provide some enlightenment.
It seems silly for a host to complain that your query is taking 100% of CPU for only a second (do you run the a lot?). Perhaps putting a small usleep(); in your while() loop will keep them happy.