There is a query, for example:

SELECT `id`,`id_category` FROM `items` WHERE `id_category` IN(22333,23433,23454 и т.д) AND `public` = 1

In each id_category there can be a set id where public == 1 Question: Is it possible to write sql, by which mysql having found the first id with public = 1, went to the next id_category in the list? those. no need to look for all the records, but only the 1st in each category.

EXPLAIN Query with DISTINCT: EXPLAIN Query with DISTINCT

EXPLAIN Query WITHOUT DISTINCT: enter image description here

  • You have a wrong understanding of the work of MySQL. - ilyaplot
  • What for? What do you want to achieve? - Anton Shchyrov
  • I want to optimize the query, because I don’t need all the records, I’m only interested in the fact that there is at least one id under this category, but the table is huge and the query will be executed very slowly since in the described example, all id will be searched - amijin
  • Do you need an id yourself? can select distinct id_category from ... if there is an index on the fields (id_catgory, public) should work quickly. - Mike
  • Add an index to the id_category field (or the composite index id_category, public - you need to look at the plan) and the query will fly - Anton Shchyrov

1 answer 1

 SELECT MIN(`id`) `id`,`id_category` FROM `items` WHERE `id_category` IN (22333,23433,23454) AND `public` = 1 GROUP BY `id_category`
  • Almost what is needed, yes, I will receive one entry from each category, but in this case, mysql will still go through all the entries to calculate the min id. And for complete beauty (in terms of optimization, reducing the time for a request), I would like mysql not to do this. But it looks like it’s not real, and I really don’t truly imagine the work of mysql - amijin
  • one
    mysql в этом случае все равно пройдется по всем записям , чтобы вычислить мин id - well, create the necessary index, what problems? - Akina