Mysql: 1000 select queries vs 1 query ( select .. in )

MySQLperformance

I have just read about N+1 queries problem. So I have the doubt about the performance of 2 ways.

The way 1: I have 1000 queries like that:

select * from topic where user_id = 1

select * from topic where user_id = 2

…

select * from topic where user_id = 1000

I supposed the topic table has 1.000.000 rows and each query uses binary search, so I think the Big O's way is:

1000 * log2(1.000.000)

The way 2: I have 1 query:
select * from topic where user_id in (1,2,…, 1000)

I don't know the algorithm of 2nd way. So can you explain me the algorithm of (select .. in) query. Is there any case the 1st way is better than 2nd way?

Best Answer

to get the same result, you need to UNION the result and you can't union 10000 selects. the limit is 63 table references per query

Further IN clause has also a limit of elements you can use.

The number of values in the IN() list is only limited by the max_allowed_packet value.

see manual

As you have many ids, you should try

select t1.* 
from topic t1
      INNER JOIN 
     (SELECT  user_id FROM topic WHERE user_id <= 1000) t2 ON t1.user_id = t2.user_id

On big numbers for the IN you will get faster results.

Related Solutions

Mysql – search for something in the first 1000 records in the table

You could use something like this:

SELECT *
FROM (
  SELECT *
  FROM your_table
  ORDER BY ...
  limit 1000
) s
WHERE name LIKE '%john%'
ORDER BY ...
LIMIT 5

Postgresql – Optimizing Postgres query

I'm trying to fetch addresses for certain users only ...

IN does not scale well with big lists. Compare performance to unnest() / JOIN

SELECT a.*
FROM   unnest(ARRAY[9681,23824,23760,20098,962,14730,12294,9552,534,
                    553,5837,6768,6583,956,24179]) AS t(user_id)
JOIN   addresses a USING (user_id)
WHERE  a.type  =  'VerifiedAddress'
AND    a.state <> 'manual_verification'

I hope state and type are at least enum types. Otherwise you should normalize your database design. Have lookup-tables for state and type and only use a small IDs referencing entries there. Makes the addresses table smaller and everything faster.

Assuming all columns NOT NULL for lack of information.

Partial index

If you need it faster still, and if there are more than a few rows with type <> 'VerifiedAddress' or state = 'manual_verification', a partial index would help some more. The gain grows with the percentage of rows you can exclude from the index.

CREATE INDEX addresses_selection_idx ON addresses (user_id)
WHERE  type  =  'VerifiedAddress'
AND    state <> 'manual_verification';

Be sure to use matching WHERE conditions in your query to allow Postgres to use this index. And weigh the benefit of tailored indexes against their cost (maintenance, extra disk space, small penalty for write operations).

Best Answer

Related Solutions

Mysql – search for something in the first 1000 records in the table

Postgresql – Optimizing Postgres query

Partial index

Related Question