Mysql select 5 rows of every item (using IN operator)

Question

I have been trying to figure this out for hours and seems that I'm stuck.

When I want to do in the query below is that I am passing some location IDs to IN operator and I don't want MySQL to return ALL the results (table is too large) but only 5 items of every location ID (if items exist).

I have temporarily put a LIMIT 100 until I get it done with your help.

Here's the query:

$sql = "SELECT SQL_CACHE deals.deal_ID, deals.slug, deals.url, deals.previous_price, deals.title, deals.image, deals.price, deals.end, deals.purchases, deals.date_added,
        deals_locations.location_ID, deals.hits
        FROM deals
        INNER JOIN deals_locations
        WHERE deals.status = 'active'
        AND deals_locations.deal_ID = deals.deal_ID
        AND deals_locations.location_ID IN (".implode(', ', $location_IDs).")
        GROUP BY deals.deal_ID
        LIMIT 100;";

Thank you very much for your time.

EDIT: Now my query output looks like this:

SELECT SQL_CACHE deals.deal_ID
    ,deals.slug
    ,deals.url
    ,deals.previous_price
    ,deals.title
    ,deals.image
    ,deals.price
    ,deals.end
    ,deals.purchases
    ,deals.date_added
    ,deals_locations.location_ID
    ,deals.hits
FROM deals
INNER JOIN deals_locations
WHERE deals.status = 'active'
AND (
    SELECT COUNT(*) FROM deals
    WHERE deals.deal_ID = deals_locations.deal_ID
) <= 5
AND deals_locations.deal_ID = deals.deal_ID
AND deals_locations.location_ID IN (1, 2, 3, 5, 7, 12, 13, 26, 27, 28, 29, 30, 31, 34, 35, 36, 38, 39, 40, 41, 42, 43, 44, 46, 49, 52, 53, 54, 55, 56, 60, 62, 64, 65, 66, 67, 68, 69, 70, 72, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 89, 90, 97, 107, 10, 21, 32, 33, 37, 51, 4, 6, 8, 9, 11, 14, 15, 16, 17, 18, 19, 20, 22, 23, 24, 25, 45, 47, 48, 50, 57, 58, 59, 61, 63, 71, 73, 84, 85, 86, 87, 88, 91, 92, 93, 94, 95, 96, 98, 99, 100, 101, 102, 103, 104, 105, 106, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160)
GROUP BY deals.deal_ID;

But still shows more than 5 records for the same location.

EDIT 2: I have tried the UNION ALL approach and seems to be working. Now I get a total result of 30 rows but I am concerned about the size of the query.

Here's what my query looks like now: http://pastebin.com/r6vyG594

The explain is huge so I can't post it online. What should I do to make the query efficient and smaller in size?

It takes about 0.2158 sec to execute.

Please don't include a signature in your posts: http://stackoverflow.com/faq#signatures — Tchoupi, Sep 22 '12 at 17:55
Possible duplicate of http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — gtgaxiola, Sep 22 '12 at 17:57
@sebastian: The $location_IDs is just a variable that contains comma separated IDs. gtgaxiola: thanks for letting me know — ggirtsou, Sep 22 '12 at 18:09

sebastian · Answer 1 · 2012-09-22T18:30:05.157

0

I can see two ways of doing this:

1 Subquery instead of php implode:

...
AND deals_locations.location_ID IN (SELECT id FROM deals_locations WHERE .... LIMIT 5)
GROUP BY deals.deal_ID
...

EDIT: MySQL does not support limit in subqueries

2 Limit your array when creating/use 5 elements in multidimensional array

implode(', ', $locations_IDs[0]) and so on.

You can also try to make one query/Id

edited Sep 22 '12 at 18:30

answered Sep 22 '12 at 18:08

sebastian

1,528
8
26
38

`IN (SELECT ... LIMIT x)` is not allowed in MySQL. – ypercubeᵀᴹ Sep 22 '12 at 18:22

score -1 · Answer 2 · answered Sep 22 '12 at 18:18

-1

To start with I would add an ON statement with your inner join:

$sql = "SELECT SQL_CACHE deals.deal_ID, deals.slug, deals.url, deals.previous_price, deals.title, deals.image, deals.price, deals.end, deals.purchases, deals.date_added,
    deals_locations.location_ID, deals.hits
    FROM deals
    INNER JOIN deals_locations ON deals_locations.deal_ID = deals.deal_ID AND deals_locations.location_ID IN (".implode(', ', $location_IDs).")

    WHERE deals.status = 'active'
    GROUP BY deals.deal_ID
    LIMIT 100;";

In the above example, I've moved both of your where statements relating to deals_locations to be in the join statement. This will limit how many records are joined from that table...

answered Sep 22 '12 at 18:18

endyourif

2,186
19
33

What's with the down vote? Regardless if it shows the same number of records the optimization in the above is extremely important to not kill a mysql server from joining too many records... – endyourif Sep 22 '12 at 18:31
I didn't downvote your answer. Thanks for helping me optimize the query. – ggirtsou Sep 22 '12 at 18:34
I wasn't necessarily referring to you! Getting frustrated with all of the down voting that seems to be happening all over SO - makes me not want to help others :( – endyourif Sep 22 '12 at 18:35

Mysql select 5 rows of every item (using IN operator)

2 Answers2