Good time. The question arose whether it is possible in the request to check whether the current (same) query contains a certain number of rows (that is, it is still in progress, but it is already known that it will return, for example, 1000+ rows), and if it contains, instead of a sample, return another result, for example select 'To much results'.

The task is not to select with the help of count, and then to check and output, but to find out dynamically

  • one
    It is known only when the request ends, it can iterate over millions of records and only at the very end apply some kind of glue or just a condition (having, for example) to them and there will be 2 records from this million or 1002 ... so such a thing as "is already clear "no sql. put the top 1000 and handle it yourself - Mike
  • You can save / cache aggregated data and make a rough estimate of these aggregated data - MaxU
  • Thanks for your reply. The fact is that I am working on a non-optimized database, which takes my logic very hard, which is generated by entityFramework (C #) - simply good

1 answer 1

Yes it can be done. But do not confuse "righteous" with "sinful." This should not be done in the request. And in front of him. Why run a heavy execution request?

Suppose you have a request of this type:

SELECT `table1`.* FROM `table1` INNER JOIN `table2` ON (`table1`.`tb2_id` = `table2`.`id`) ...

Before doing it, do:

 SELECT COUNT(`table1`.`id`) FROM `table1` INNER JOIN `table2` ON (`table1`.`tb2_id` = `table2`.`id`) // по возможности убирая излишние JOIN-ы ...
  • In general, the solution seems strange. Instead of the 1st heavy query, two heavy queries will be executed. - Viktorov
  • So you do not understand the concept of the database. The first request will be executed quickly at times. Since he will not need to transfer data, but only one number. And the second will be executed due to the fact that the tables from the first query will get into the MySQL-i cache. And if you want to be smart, then there is no limit to optimality, make a bunch of adjacent tables where you store the number of answers for each of your queries. You asked for advice or just pofludit like? - Makarenko_I_V
  • Explain that I understood not so. There is a heavy query that you want to perform if it returns the required number of rows, and kill if there are more rows than you need. Based on the desire to kill him, I assume that he is heavy. Further you suggest to replace 1 request with another. I do not understand why the first request will be executed many times faster than the second, please explain. And nowhere is it said that the main load when requested goes to the disk subsystem. I don’t say that the decision is bad, I say that I don’t understand why it is good in general and I ask you to explain or send by reference - Viktorov
  • @lDrakonl download the book and read. If conditionally all simplify the request can be divided into several parts. 1 - search for a query in the cache, 2 - loading of the necessary tables, 3 - execution of the query itself, 4 - preparation of data for a response. 5 - transmission of the answer. My request will save you 4 and 5 stages. If the number of entries allows you, only then you will select all the data for this query (while saving on clause 2 ). - Makarenko_I_V