Sven Ya Sven Ya - 4 months ago 9
MySQL Question

Setting a LIMIT before JOINs of tables

I have the following tables:

client_purchases:

id_sale | id_client | timestamp


files_purchases:

id_sale | id_file


So with one purchase of the client he can buy many files and the files can be bought several times.

I select what I want like this:

SELECT cp.id_sale, fp.id_file
FROM client_purchases AS cp
JOIN file_purchases AS fp
ON cp.id_sale = fp.id_sale;


Works just fine. What I get is something like this:

id_sale | id_file
1 1
1 2
1 3
2 1
3 1


Now to make sure that it doesn't take forever to look through my database if it grows I wanted to limit the amount of rows.

SELECT cp.id_sale, fp.id_file
FROM client_purchases AS cp
JOIN file_purchases AS fp
ON cp.id_sale = fp.id_sale
LIMIT 0,25;


Whick returns me 25 rows. But what I acctually want is 25 different "
id_sale
". So is there a method to tell SQL to count the
DESTINCT
values of a column and stop if that value reaches a specified number? And I do need to be able to set the start and end value of the
LIMIT
.

Answer

You can use JOIN + Subquery

SELECT cp.id_sale, fp.id_file 
   FROM (SELECT id, id_sale FROM client_purchases ORDER BY id LIMIT 25) AS cp 
JOIN (SELECT id FROM file_purchases ORDER BY id LIMIT 25) AS fp 
    ON cp.id_sale = fp.id_sale 

However this may speed up your query or it may make it go even slower. It all depends on what kinds of indexes you have and how many records you have in the table.

What seems fast with 100 records might be slow with 100M records and vice verce.

Comments