EXPLAIN EXTENDED

How to create fast database queries

My latest article on SQL in general: Things SQL needs: determining range cardinality. You're welcome to read and comment on it.

Passing parameters in MySQL: IN list vs. temporary table

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

«
»

Comments are closed.