Good day! There is a table of products fields:

id - PRIMARY
manufacturer - manufacturer of goods INDEX
code - product code INDEX
price - the price of goods

The table contains more than 200 million items, with some codes, manufacturers are interchangeable (i.e., several manufacturers may have one code and in fact are the same product).

What is the problem: when prompted

SELECT * FROM products WHERE `code` = 'ABC1' ORDER BY `price` LIMIT 2000;

sorting is by price, and suppose that if SONY has about 3000 of the cheapest titles, the other manufacturers will simply not be included in the sample. In fact, the request is not so simple, but takes several pages, a lot of highload services are tied up on it and it is difficult to change it in the bud, but if you simplify, it will turn out like this.

What I have tried:

2 variants of stored procedures:
1) sort the request first by manufacturer, then by price, transfer the request to the procedure, get the cursor, count the number of manufacturers in LOOP and insert the necessary records into TEMPORARY TABLE, then select the result from it. Problem: the cursor can only be obtained from select_statement, dynamic SQL does not roll

2) transfer the request to the procedure, fill it with the request TEMPORARY TABLE, then the magic, at the output of which a request of the form is formed:
DELETE FROM tmp_tbl WHERE manufacturer = 'SONY' LIMIT 200, 100000;
DELETE FROM tmp_tbl WHERE manufacturer = 'PIONEER' LIMIT 200, 100000;
.......

That is, for each brand DELETE with a limit. But here is the problem: DELETE in MySQL is not able to OFFSET. He cannot skip 200 lines and delete the rest.

Actually the question is: how in one request to limit the number of displayed records to 200 by manufacturer? For example, if a SONY has 3000 items, then the first 200 are chosen, etc. Producers of one code can reach hundreds.

    1 answer 1

    The easiest way is to number the records within the manufacturer in the order of prices using variables.

     SELECT * FROM ( SELECT P.*, @num:=if(@man=manufacturer,@num+1,1) as NUM, @man:=manufacturer FROM products P, (select @man:='',@num:=0) A WHERE `code` = 'ABC1' ORDER BY manufacturer, `price` ) A WHERE NUM<=200 ORDER BY price
    • Cool! Thank! only in the following join does not see for some reason, SELECT * FROM ( SELECT P.*, @num:=if(@man=manufacturer,@num+1,1) as NUM, @man:=manufacturer FROM products P, (select @man:='',@num:=0) A INNER JOIN sclad ON (P.sclad = sclad.id) WHERE code` = 'ABC1' ORDER BY manufacturer, price) A WHERE NUM <= 200 ORDER BY price ` - Alex Shmalex
    • @AlexShmalex Commas and join should not be mixed. Something like ... FROM products P JOIN (select @man:='',@num:=0) A INNER JOIN sclad I think because of this. or it can rearranged in some places ... (select @man:='',@num:=0) A, products P INNER JOIN sclad - Mike
    • it was not possible to change places, but JOIN helped, and it is important that it is the most recent - Alex Shmalex