with databases with many records (millions), if I make a query with " likes ", the webapp blocks until the query is complete and also blocks the accesses of other users or other sessions.
I also tried to open the database by setting a timeout proprierty =10 seconds, and trying to intercept the error that I expected would be raised by a too long query, but unfortunately this is not the case and no exception is raised after 10 seconds . I use mysqlcommunityserver and rowsource. Anyone have any idea how to handle this?
it depends on your use case but I allways have a LIMIT keyword on querys, specially for web. Also a counter and if the rows are = to the Limit, show a message, “showing first X rows”, please be more specific on your search. Or when I need to have all the records available, I use Pagination.
no, I explained myself badly …
I have a database with 20 million records in one table, tableA
if I make a query making me return all the records, 20 million records, the app code waits for the creation of the rowsource containing 20 million records, and block the webapp for all other users
dim rs as rowsource
rs = mysqldb.sqlselect (“select * from tableA”)
we assume that the time to complete the query is 30 minutes, (in this time the webapp will also be blocked for other users)
well, I would like to be able to set a timeout beyond which the quesry is aborted to avoid blocking the entire webapp, I tried with the property db.timeout = 10 seconds, but obviously the queries are not affected by this timeout
ok, inspired by another forum reply, i found this code on the mysql forum
select
/* + MAX_EXECUTION_TIME (1000) */
- from tableA
1000 is milliseconds, when the query exceeds 1000 milliseconds, mysql stops and throws an error
There is a Timeout property on the MySQLCommunityServer plugin. Since you say it’s not working for the queries that’s odd since there is no docs stating what’s it for.
This could also help:
For example:
SET GLOBAL MAX_EXECUTION_TIME=1000;
Then any SELECT statement run against this MySQL instance will be aborted if it takes more than 1 second to complete. The default for the GLOBAL variable is 0, which means that there is no global time limit.
For example:
SET SESSION MAX_EXECUTION_TIME=2000
Not sure what Xojo’s MySQLCommunityServer.Timeout is meant for but probably for pre-connection.
iI was looking for something that was limited to the query I was doing rather than a global attitude.
/ * + MAX_EXECUTION_TIME (1000) * /
solves the problem if inserted into the query
1 Like
usually you would filter the data with the WHERE part.
as example select * from tableA where year(datefield)>2020
why do you use this? rs = mysqldb.sqlselect (“select * from tableA”)
you could also add indices in database to the field that you will search to get a faster response.
I just gave an example, I have a table with about 20 million records and I have to do some searches with the like operator, and it is known that this type of query takes a long time, even with indexed fields. Since the webapp freezes (for all users) when running a very heavy query, then I needed a system to stop the quesry in case more than 30 seconds have passed since it started.
that is odd, seems the db block something. it is just a select.
does mysql start a transaction?
does SELECT SQL_TIMEOUT=30 * FROM tableA work for you? (i found it in www about mysql)
i had a old database in past where reorganizing indexed helped, but it was ms sql.
this solve in mysql
/ * + MAX_EXECUTION_TIME (1000) * /
ok.
ups, i misread before.
1 Like