-5

I have very big database, and my users can sample from this database. They build very large queries that link about 30-40 tables. The result of the query sometimes reaches 2 minutes. I optimized the server as much as possible, but still the data transfer rate is very low.

So I made a visual effect of the query, so that the user could save the request, and the result will be sent to him in the browser when the query is executed.

But there is one problem. I do not know how to make a database scan for the execution of the request.

I created the Event system. I bookmark events in the database and then process them. Separately, I did a database scan through the cron.

But the problem of the cronis that it does not have time to work in 1 minute and a new cron is launched and this increases the load on the server and creates a recursion.

I want to create a php task so that after saving a request from the user it starts executing it, but only after the event is created for its execution.

Could you please, how do I better do this, what methods can help me in this.

Thanks

  • Possible: Create a Queue table. Place the queries on that. Allow only 1 or 2 ... crons to be running at one time to process queries from the the queue. – RiggsFolly Aug 20 '18 at 13:42
  • Also possible: mysqli has a `MYSQLI_ASYNC` flag so you can run mysql asynchronous. [see this question](https://stackoverflow.com/questions/27240421/php-asynchronous-mysql-query). *checkmate PDOer's! We have a one up on you* =) – IsThisJavascript Aug 20 '18 at 13:44

1 Answers1

1

I would use a framework such as Laravel and take advantage of its queue system.

https://laravel.com/docs/5.6/queues#job-events

There is already one implemented for databases.

"Using the before and after methods on the Queue facade, you may specify callbacks to be executed before or after a queued job is processed.".

I guess this can give you an idea about what to do after the query is processed.

javier_domenech
  • 5,995
  • 6
  • 37
  • 59
  • Seems a bit overkill especially since OP has not specified what (if any) framework they are using. I sit on the edge with this answer. – IsThisJavascript Aug 20 '18 at 13:49
  • I see your point, it's just to give some address where to start. Briefing, a queue system. – javier_domenech Aug 20 '18 at 13:52
  • @vivoconunxino Yes, I use Laravel. Those. if I create a queue after saving, then logically it will work? –  Aug 20 '18 at 14:33