Welcome to WebmasterWorld Guest from 50.17.114.227

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

WHERE Condition limit, in PHP call to MySQL DB

     
12:49 am on Apr 5, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 20, 2008
posts: 172
votes: 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?
5:53 am on Apr 5, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 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
10:41 pm on Apr 5, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 20, 2008
posts: 172
votes: 0


So, does anyone else have any ideas on this?
12:16 am on Apr 6, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


I have never come across one
12:22 am on Apr 6, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 12, 2005
posts:5966
votes: 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...]
5:37 am on Apr 6, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 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
4:32 pm on Apr 6, 2011 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 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.