Page 1 of 1

mysql IF statements

Posted: Tue Apr 10, 2007 10:44 am
by GeXus
Does anyone use if statements a lot in mysql? What I typically do, is a select.. then in php do statements based on that data, and issue more queries if necessary (which in some cases, can result in 10's of potential queries).. however, I suppose this could all be done on the sql side as well.. any feedback about pros and cons would be great.

Thanks!

Posted: Tue Apr 10, 2007 12:18 pm
by Begby
One of the most resource intensive things you can do in PHP are queries. 10 queries are a lot slower than doing 1 query and then doing a bunch of processing on that 1 query.

Probably the worst thing you can do is do a query, then loop on the results of that query and do another query for every record in the result set. I see this done so many times in code I fix. People do this because they don't see a slow down when they are testing 10 records, but when the database grows it becomes a huge bottle neck. A simple join is usually the fix for this.

So I guess the short answer is doing one optimized query is a lot better than doing a bunch of queries to accomplish the same thing.

Posted: Tue Apr 10, 2007 5:04 pm
by RobertGonzalez
I am moving all of my code to stored procedures, so all of my conditional logic is in the proc. And it makes it a helluva lot faster.

Posted: Tue Apr 10, 2007 9:44 pm
by GeXus
Everah wrote:I am moving all of my code to stored procedures, so all of my conditional logic is in the proc. And it makes it a helluva lot faster.
Are stored procs supported in 4.1? ... I really think it's time to upgrade!

Posted: Wed Apr 11, 2007 2:27 am
by onion2k
MySQL IF statements are exactly the same as PHP ternary if statements. They're very useful; I use them a lot.

Posted: Wed Apr 11, 2007 2:28 am
by onion2k
GeXus wrote:Are stored procs supported in 4.1? ... I really think it's time to upgrade!
MySQL 5.0 and above I think.

Posted: Wed Apr 11, 2007 10:39 am
by RobertGonzalez
Yeah, MySQL 5+. They got better in MySQL 5.1 I believe, though I think that is still in Beta. I am running 5.0.17 on my home machine and 5.0.22 on my production servers.

Posted: Wed Apr 11, 2007 3:58 pm
by timvw
GeXus wrote: Are stored procs supported in 4.1? ... I really think it's time to upgrade!
Yeah, upgrade to postgresql ;)