homepage Welcome to WebmasterWorld Guest from 54.145.183.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
WHERE Condition limit, in PHP call to MySQL DB
username

5+ Year Member



 
Msg#: 4292119 posted 12:49 am on Apr 5, 2011 (gmt 0)

Hi, I am dealing with some MySQL tables with large amounts of columns. I am wondering is there a limit to the number of conditions a MySQL SELECT statement can handle?

i.e. SELECT * FROM table WHERE something = "1" AND somethingelse = "2" AND ... AND ... AND ...;

Is there a way to optimise this, knowing that the table could have 150 columns?

 

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4292119 posted 5:53 am on Apr 5, 2011 (gmt 0)

Hi there Username,

I have no idea, and never really thought about that - personally I would post this in the databases section, as there are people in there who may answer this or show a way of optimising this for you.

I have seen queries get quite large though, so possibly this is a natural thing for mysql to handle.

Sorry I can't be more helpful.

Cheers,
MRb

username

5+ Year Member



 
Msg#: 4292119 posted 10:41 pm on Apr 5, 2011 (gmt 0)

So, does anyone else have any ideas on this?

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4292119 posted 12:16 am on Apr 6, 2011 (gmt 0)

I have never come across one

eelixduppy

WebmasterWorld Senior Member eelixduppy us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4292119 posted 12:22 am on Apr 6, 2011 (gmt 0)

All I could find....


A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

The largest possible packet that can be transmitted to or from a MySQL 5.0 server or client is 1GB.


[dev.mysql.com...]

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4292119 posted 5:37 am on Apr 6, 2011 (gmt 0)

>>The largest possible packet that can be transmitted to or from a MySQL 5.0 server or client is 1GB

Well I shan't worry about sql queries being over 100 or so lines again!

Nice find EelixDuppy, and thanks for sharing.

Cheers,
MRb

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4292119 posted 4:32 pm on Apr 6, 2011 (gmt 0)

Like most things mySQL, limitations are more likely to be hardware, OS, and memory dependent than any set limits.

If you have tables 150 columns wide, and that many "and's" or "or's" in your selects, it may be a good time to re-think your database structure and split it up into multiple tables. Look into database normalization.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved