How to Intentionally Create a Long-Running MySQL Query

I know this is an odd question to ask, but I'd like to find out if there is a MySQL query I can create without already having millions 开发者_Go百科of rows in my database that will consume resources and run for a long time. My ultimate goal is to test my application in cases of resource contention and make sure that my methods for handling failure (specifically server timeout) are correct.

If there another way I can test this without creating and executing a high-resource query, I'd appreciate hearing that as well.

I assume you could use benchmark() for this, although that isn't the scope of it.

select benchmark(9999999999, md5('when will it end?'));

If you need them to run for a bit, but not that long, then try

select benchmark(1000000, md5('when will it end?')); -- around 0.3s
select benchmark(10000000, md5('when will it end?')); -- around 3s
select benchmark(100000000, md5('when will it end?')); -- around 30s

For those wondering how to achieve a similar operation for MS SQL Server (not MySQL), use WAITFOR DELAY '02:00' if you want to test timeouts.