How To Get Ids Of 4 Distinct Values ( Combination Of 2 Keys )

I got table like below mysql> select * from tb_dts; +----+------+------+ | Id | key1 | key2 | +----+------+------+ | 1 | 1 | 1 | | 2 | 1 | 1 | | 3 | 1 | 1 |

Solution 1:

For your immediate problem, you can use find_in_set like this:

select t.*
from your_table t
whereexists (select1from (
        select group_concat(Id)
        from tb_dts
        groupby key1, key2
        order by key1, key2   -- very important whenusing limit
        limit 0, 4
    ) t2 wherefind_in_set(t.fieldname, t2.ids) > 0
);

Though I am not sure if this is the best way to do what you're doing. Creating strings using group by and then searching in that string will be too slow.

Also, you want to have an index on key1, key2, id columns.

create index idx_tb_dts ontb_dts (key1, key2, id);

Can try this:

select t.*from your_table t
whereexists (
    select1from tb_dts t1
    innerjoin (
        selectdistinct key1, key2
        from tb_dts
        orderby key1, key2
        limit 0, 4
    ) t2 on t1.key1 = t2.key1
    and t1.key2 = t2.key2
    where t1.id = t.fieldname
);

You should understand that the group by or distinct part can be heavy on performance. It will be much better if there was a separate table containing unique key1, key2 with a unique index on them.

createtable the_keys_table(
    key1 intnotnull,
    key2 intnotnull,
    primary key (key1, key2)
);

Then you could replace the tb_dts in below with that table like this:

select key1, key2       -- no distinctorgroupby needed.
from the_keys_table
orderby key1, key2
limit 0, 4

Your final query becomes:

select t.*
from your_table t
whereexists (select1from tb_dts t2
    where (key1, key2) in (select key1, key2
        from the_keys_table
        order by key1, key2
        limit 0, 4) and t1.id = t.fieldname
);

Solution 2:

you question seems a little confusing but from my understand if what you want is to use the ids in a WHERE field IN clause you can make use of subqueries. So in your case where you had select key1,key2,group_concat(Id) from tb_dts group by key1,key2 limit 4,4; that can simply become

select field 
from table_name 
where id IN 
  (select group_concat(Id) 
   from tb_dts groupby key1,key2 limit 4,4)

You can find google subqueries to find out more. Let me know what you think about this approach. Hopefully this could be a step.

Solution 3:

you can try this

select  *
    from  tb_dts
    where  Id IN (
        SELECT  r2.Ids from
                  ( SELECT  group_concat(Id) Ids
                    from  tb_dts as r1
                    groupby  r1.key1,r1.key2
                    limit  4,4
                  ) r2 
                 );

Solution 4:

Step 1: Abandon the pagination scheme you envision. Instead, paginate on key, not "4 rows per page".

Step 2: "Remember where you left off". If the first page has all the pairs for key1 = 1 or 2, then the second page starts with the next value after key1 = 2.

Now the query becomes much more efficient since it is over a "range" of key1 values. Currently, it must build the entire output before it can paginate!

More discussion of pagination without using OFFSET.