[thelist] MySQL stored function looping problem

Phil Turmel pturmel-webdev at turmel.org
Wed Dec 10 06:43:52 CST 2008

[Sorry for the slow response... no signal / no internet all day yesterday]

Jeremy Weiss wrote:
> Phil Turmel wrote:
>> I think you've made this a little more complicated than necessary. 
> I do have a tendency of doing that. :(

Heh.  "This is a cool hammer!  That sure looks like a nail!"

>> This is very much a scripting task as opposed to a 
>> SQL task (not that it can't be done in SQL...)
> I'm having a difficult time distinguishing where that line is.
> How you tell the difference between the two?

The line varies with circumstances and languages involved, but in the
SQL vs. script case I usually ask myself the following questions:

Do I need a stored proc to enforce critical business rules?
Do I need to hold a lock for the duration of the procedure? (I *really*
don't want a lock waiting for the network)
Can I structure a stored proc for straight through evaluation? (I hate
loops in SQL)
Which language has the better tools for the task at hand? (i.e. PHP's
'explode' function)

>> Yeah, there'll be a bunch of single-row inserts issued from PHP, but for
>> a one-time conversion script, I doubt you'll suffer too much.  You did
>> say there's only ~ 15K listings.  Figure 1ms per insert to estimate the
>> runtime.  (If I'm way off on that, I'd like to know what hardware you're
>> running on...)
> I don't know if it changes anything, but this would be ran once daily, 
> right after I pull the feed from the Realtor Assoc. It averages ~15K 
> properties but that translates into ~450K inserts into the 
> carmls_features table.
>  I've never timed INSERTS on my server before, but you got me 
> curious. So, I gave it a go using your code (which worked great,
> btw) and here's what I saw:
> 453866 records inserted
> total time 98.8076000008 seconds
> average time per insert = 0.000217702141162 seconds

Nice.  Is this with or without an existing key on mls,fid?

> That's a lot faster than I thought it would be, but wouldn't it be
> faster if it wasn't going back and forth between PHP and MySQL?

If PHP is on the same server as MySQL, I've never seen any significant
difference.  If different boxes, even on a gigabit lan, going back and
forth will certainly slow you down.

However, MySQL does support multiple values per insert statement, so the
round-trips could be reduced with modest additional script complexity.

> -jeremy



More information about the thelist mailing list