EXPLAIN EXTENDED

How to create fast database queries

Passing parameters in MySQL: IN list vs. temporary table

with 7 comments

When you need to pass a list of parameters into a MySQL, there are at least two method to do this:

  • Issue a dynamical query which would fill an IN list with constant values
  • Fill a temporary table with these values then use it in a JOIN

The second method is considered better for many reasons (you can easily reuse the values, it's more injection-proof etc.)

Leaving all these issues aside, let's compare these methods performance-wise.

We will create a table of 50,000 records, pass a list of 500 parameters (using both methods) and see which is faster:


Table creation details

We have created two stored procedures.

The first procedure, prc_temporary, fills the temporary value using a dynamic query then issues the query using a JOIN in a loop.

The second procedure, prc_range, just uses the IN list in a dynamic query, also in a loop.

Let's run both procedures:

CALL prc_list(1000)
_cnt
1001
1 row fetched in 0.0001s (4.1218s) 
CALL prc_temporary(1000)
_cnt
1001
1 row fetched in 0.0001s (1.9406s)

However, if we change the procedures and leave but 20 parameters:

Changing procedures here

, both procedures have almost the same performance:

CALL prc_list(1000)
_cnt
1001
1 row fetched in 0.0001s (0.6315s) 
CALL prc_temporary(1000)
_cnt
1001
1 row fetched in 0.0001s (0.6408s)

We see that for a large list of parameters, passing them in a temporary table is much faster that as a constant list, while for small lists performance is almost the same.

Using a temporary table is the best way to pass large arrays of parameters in MySQL.

Written by Quassnoi

August 18th, 2009 at 11:00 pm

Posted in MySQL

«
»

7 Responses to 'Passing parameters in MySQL: IN list vs. temporary table'

Subscribe to comments with RSS

  1. You could provide an SQL example

    Alon

    24 Apr 13 at 15:51

  2. @Alon: click on the links (“Table creation details”) and (“Changing procedures here”)

    Quassnoi

    24 Apr 13 at 16:03

  3. Isn’t mysql internally creating a temp table even with IN (LIST) syntax? If you perform an EXPLAIN on any query with the IN list statement, you will see the “using temporary” message. Based on that I would think there has to be more to this performance difference. Perhaps the temp table that Mysql is using also has a unique index on the values, which would avoid redundant / duplicate joins and save on query time but lose on time to create the unique temp table values. It would be interesting to see the results of your test if you also added unique indexing on your temp table. I would think the methods would be on par at that point.

    Mike

    30 Jun 13 at 12:03

  4. Not sure where is a bottleneck in your procedure,
    but IN (with,constants, here) may not be a culprit.
    Maybe indexes?

    I just run a query with some 2100 constants (yes, 2 zeros – all integers) on a temp table 11.000 rows and it took some 0.4 sec to complete.

    I must add, that query had 7 tables involved 2 of which were LEFT JOINed and it was quite heavy with filters: multiple BETWEENs,”=”, even some SELECT GROUP_CONCAT() subqueries.
    On top of above, query run BOOLEAN FULLTEXT search on 2 tables, which is not speediest of them all.

    0.4 sec is not that bad.

    Jeff Parr

    18 Oct 13 at 23:53

  5. Does this still hold true 8 years later?

    Yang

    10 Nov 17 at 16:13

  6. Honestly I don’t know but there are scripts in the article which make it easy to set up the same experiment on a newer version of MySQL.

    Why don’t you run them and share your findings with us?

    Quassnoi

    14 Nov 17 at 14:02

  7. Not true on mariadb 10.4.22 for example, probably not with current mysql versions either.
    CALL prc_list(1000);
    is at least 6 times faster than
    CALL prc_temporary(1000);
    when testing with 500 parameters.

    Heihei

    9 Feb 22 at 16:36

Leave a Reply