select all rows with lowest product_id

Question

i have this table and i want to select all rows with product id 105. i can easily do this if i know product id and put in where clause but i dont know product id and it increases once this id work is completed it will delete these rows and search for next lowest product id.

with order bt product_id i can do but how to ensure that all rows are listed. if i will give 100 limit then first 10-15 may be lowest after that it will start listing next higher product_id rows

here is table

id   name  product_id some_columns
1    dff   105          gfff
2    fg    109          ffgfgf
3    tt    106          gttytt
4    tt    105          trtr
5    trr   112           trrrt
6    rrr   111           rttttr
7    ttyt  108            ttrtrtr
8    rrrr  105            rrerer






   SELECT id, name, product_id, some_columns 
   FROM table_name 
   WHERE product_id = (SELECT MIN(product_id) FROM table_name)

here you can see that lowest product_id is 105 but i dont have any control on how many times it will appear in table. in some case it may be 10 rows and in some case it may be 250 rows.

so order by product_id will not work. as giving limit will list all id initial few rows may be 105 but after than when all 105 rows are listed it will start listing all rows which is higher than 105

the best solution would be if i could use where product_id=105 but my bad luck i dont have any control on product id so cant use product id in where clause. next big problem is i want to use it efficiently so we have indexed product_id column i was exploring min value option but i am highly doubtful about its efficiency and probable affect on mysql

so any help will be great

You could always benchmark. If it’s indexed it shouldn’t be a huge issue using min, but it all depends on the data and amounts. — Sami Kuhmonen, Aug 01 '19 at 06:30
you can find lowest product id using sub query and use in where condition. — Devsi Odedra, Aug 01 '19 at 06:30
@DevsiOdedra it will simply double the query. i want to use least possible. its 20 millions row table — arijit995, Aug 01 '19 at 06:31
@SamiKuhmonen 20 million rows in that table so i want to use minimum — arijit995, Aug 01 '19 at 06:32
Have you benchmarked or at least looked at the query plan? And why would it “double the query”? — Sami Kuhmonen, Aug 01 '19 at 06:33
@SamiKuhmonen for making inside call we are making select and it is not double work to mysql ?? — arijit995, Aug 01 '19 at 06:34
`min()` can use the information in an index. It’s not “double work” if it just looks up a minimum value from an index. You always have to test, not assume. — Sami Kuhmonen, Aug 01 '19 at 06:35
@SamiKuhmonen as expected mysql select shows that two queries that is my biggest worry. PRIMARY and SUBQUERY — arijit995, Aug 01 '19 at 06:40
@SamiKuhmonen included. i was wrong though. https://stackoverflow.com/questions/1992312/meaning-of-select-tables-optimized-away-in-mysql-explain-plan says that database already keeps the maximum and minimum count in database so including minimum and maximum in query does not causes any extra load on mysql., learnt something new . thanks — arijit995, Aug 01 '19 at 06:45

score 1 · Accepted Answer · edited Aug 01 '19 at 06:36

1

You can try something like

SELECT id, name, product_id, some_columns 
FROM table_name 
WHERE product_id = (SELECT MIN(product_id) FROM table_name)

As far as I understood you want to select all the rows that match the minimum product_id. Be it 101, 102 or whatever, it's uncontrollable, so you get the minimum product_id in the row and then you select the rows that has the current minimum product_id.

edited Aug 01 '19 at 06:36

James

1,819
2
8
21

answered Aug 01 '19 at 06:34

M. Suleiman

858
4
22

what about the performance of this query. i am 100% skeptical in using this because i feel SELECT MIN(product_id) FROM table_name is causing extra work and will cause serious performance issue in mysql – arijit995 Aug 01 '19 at 06:36
as expected mysql select shows that two queries that is my biggest worry. PRIMARY and SUBQUERY – arijit995 Aug 01 '19 at 06:40

ScaisEdge · Answer 2 · 2019-08-01T06:57:35.423

0

You could try using a subquery for min_id group by product

 select m.* 
 from table_name  m
 inner join (
   select min(id) min_id, product_id 
   from  table_name 
   group by  product_id 
 ) t on t.min_id  = m.id

edited Aug 01 '19 at 06:57

answered Aug 01 '19 at 06:46

ScaisEdge

131,976
10
91
107

min and max in mysql does not count any extra resources as it is already stored in database so as per this thread i feel inner join is not required . ur advise in my view will be expected if i am wrong in anyway https://stackoverflow.com/questions/1992312/meaning-of-select-tables-optimized-away-in-mysql-explain-plan – arijit995 Aug 01 '19 at 06:51
try explain better you comment ... based on your sample you have two entry for product 105 .. and if you want find each min id for each product_id you need a relation with a min id result ..as in my answer ... . (the link in yoru comment seems not related to the your questin) ... (min and max are not stored in db ... are calculated ... ) – ScaisEdge Aug 01 '19 at 06:55
From the MySQL documentation: The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned. Basically this means your query uses data that is directly available to MySQL and the query will run in constant time. – arijit995 Aug 01 '19 at 07:03
It means the table is completely optimized out of the query. You can’t get any better than that. – arijit995 Aug 01 '19 at 07:04
@arijit995 ... for what i know i think you have not clear idea about this query ... .. try the query form both answer .. check for the correct result .. the query you have marked return just the row for the lowest id in the table ...mine return the rows for all the produc using the lowest id for each product ... and if you have performance problem add the proper index ... ... – ScaisEdge Aug 01 '19 at 10:27

select all rows with lowest product_id

2 Answers2