CODEWITHSUNDEEP
phpmysqlmariadb
PlatformDating
PlatformDating

Reputation: 197

MySQL (MariaDB) execution timeout within query called from PHP

I'm stress testing my database for a geolocation search system. It has a lot of optimisation built in already such a square box long/lat index system to narrow searches before performing arc distance calculations. My aim is to serve 10,000,000 users from one table.

At present my query time is between 0.1 and 0.01 seconds based on other conditions such as age, gender etc. This is for 10,000,000 users evenly distributed across the UK.

I have a LIMIT condition as I need to show the user X people, where X can be between 16 and 40.

The issue is when there are no other users / few users that match, the query can take a long time as it cannot reach the LIMIT quickly and may have to scan 400,000 rows.

There may be other optimisation techniques which I can look at but my questions is:

Is there a way to get the query to give up after X seconds? If it takes more than 1 second then it is not going to return results and I'm happy for this to occur. In pseudo query code it would be something like:

SELECT data FROM table WHERE ....... LIMIT 16 GIVEUP AFTER 1 SECOND

I have thought about a cron solution to kill slow queries but that is not very elegant. The query will be called every few seconds when in production so the cron would need to be on continuously.

Any suggestions?

Upvotes: 4

Views: 10208

Answers (1)

Asur
Asur

Reputation: 4017

Using MariaDB in version 10.1, you have two ways of limiting your query. It can be done based on time or on total of rows queried.

By rows: 

SELECT ... LIMIT ROWS EXAMINED rows_limit;

You can use the keyword EXAMINED and set an amount of lines like 400000 as you mentioned (since MariaDB 10.0).

By time:

If the max_statement_time variable is set, any query (excluding stored procedures) taking longer than the value of max_statement_time (specified in seconds) to execute will be aborted. This can be set globally, by session, as well as per user and per query.

If you want it for a specific query, as I imagine, you can use this: 

SET STATEMENT max_statement_time=1 FOR 
  SELECT field1 FROM table_name ORDER BY field1;

Remember that max_statement_time is set in seconds (just the opposite of MySQL, which are milliseconds), so you can change it until you find the best fit for your case (since MariaDB 10.1).

If you need more information I recommend you this excellent post about queries timeouts.

Hope this helps you.

Upvotes: 7

Related Questions