Best practice to get count of rows in mysql if we have large list to be passed

user_11278696 0
Viewed 331

I want to get the count of rows that has sec_id and the ref_id should be present in the list say ref_list. The size of ref_list is around 300. The size of table is around 70,000.

I am currently using this technique to get the count of rows satisfying the above condition.

SELECT count(*) FROM table where sec_id=? and ref_id IN (?,?,...,?)

It is taking in between 20ms to 30ms to execute.

But this query will be executed for around 1200+ times, so the overall time is becoming around 45s to 50s to execute.

Is there any other way that i can reform the above query so that it may take less time?

The table is indexed with id as primary key and ref_id of key type as multiple

Database -> MySQL

jpa hibernate mysql sql
3 Answers

Create a specific index for all the conditions in your query.

In your case:

    create index your_index_name on table (sec_id, ref_id);

Try to avoid adding functions inside your where conditions. Functions may not use the index.

Also consider make use of mysql query caching if some of your recurrent request are equals.

user_96959730

Don't run multiple queries. Create a temporary table with all the values of sec_id/ref_id and then run a single aggregation query. Assuming you want one row per sec_id, then:

select t.sec_id, count(t.sec_id)
from sec_ref sr left join
     t
     on t.sec_id = sr.sec_id and t.ref_id = sr.ref_id
group by t.sec_id;

This will take way more than 20-30 milliseconds. But you will only need to call it once.

user_11440350

I agree with Pau Seglar: creating an index on sec_id, ref_id columns is your way. By the way: you can choose between two options:

  • creating two simple indexes - one on column sec_id and another on ref_id;
  • creating one composite index on sec_id, ref_id

Both options will work for you. But a composite index will probably work a bit faster for this query. A disadvantage of a composite index is that it is perfectly fine for requests like:

SELECT COUNT(*) 
FROM table 
WHERE sec_id = ? AND ref_id IN (?,?,...,?)

or

SELECT COUNT(*) 
FROM table 
WHERE sec_id = ?

bot it will not be used for

SELECT COUNT(*) 
FROM table 
WHERE ref_id IN (?,?,...,?)

(If first column in index is not used in WHERE)

user_74051820
Related