MySQL Point

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.

(more…)