mysql IF statements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

mysql IF statements

Post 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!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

MySQL IF statements are exactly the same as PHP ternary if statements. They're very useful; I use them a lot.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

GeXus wrote: Are stored procs supported in 4.1? ... I really think it's time to upgrade!
Yeah, upgrade to postgresql ;)
Post Reply