Author |
Topic |
PC-fre@k
Starting Member
5 Posts |
Posted - 2004-04-14 : 12:11:12
|
Hi all, found this site and I hope one of you could help me to solve the following problem:The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok The query:$qry=$DB->sql_qry("SELECT p.*,COUNT(c.id) AS num,k.kategorie AS katname FROM ".$this->sql_table["pics"]." AS p LEFT JOIN ".$this->sql_table['comments']." AS c ON p.id = c.pid LEFT JOIN ".$this->sql_table['kategorie']." AS k ON p.kategorie = k.id $this->katadd GROUP BY p.id"); (part of the PHP)It is a photo script, it shows photos/hits/comments and if they are all together more then 4500 the error will show up. Íf I make sure it is below that, no problems at all. I have a Forum running with 13000 posts so I think it isn't my database/server..How should I set that option? Add somthing? I tried to find this solution on google without succes. I hope I'm on the right place now!Thanks |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-14 : 12:39:42
|
You doing SQL Server? What platform?1st I would suggest this be done in a stored procedure, but never mind thatHow come you don't have a predicate?Your results will be ALL rows...Also, never do SELECT *Look up Paging here...but you should provide something to redeuce the result set to what they want..For example, you say you'be got 4500 rows...So what, I have to page through to the row I want? Which happens to be 4234?I'm not sticking around that long...Brett8-) |
 |
|
PC-fre@k
Starting Member
5 Posts |
Posted - 2004-04-14 : 12:59:36
|
Well it is a PHP script made by someone else..Picture Solution, www.script-solution.deI asked him how to fix it, but he even doesn't know about the error.I'm a total SQL N00B, I can fix some php or combine scripts thats all...It is on my webserver, System Debian GNU/Linux - Bi PIII 1Ghz - 1 Gb RAM - Kernel 2.4.x [url]http://pc-freak.pc-freakz.com/DBinfo.txt[/url] |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-14 : 13:03:06
|
I believe that there is only one PHP person who frequents SQLTeam. Just so that you are aware, this site is for SQL Server, so your question may go unanswered here. I'm not sure if there is a PHP forum out there. Brett, does dbforums.com have one?Tara |
 |
|
PC-fre@k
Starting Member
5 Posts |
Posted - 2004-04-14 : 13:05:26
|
Ah, wrong place...most ppl I ask dunno about this error, it seemed professionalout here I thought Just give it a try :P |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-14 : 13:08:57
|
Can you limit your result set by adding a WHERE clause to avoid the error? Are your tables properly indexed? This is where I would start troubleshooting.Tara |
 |
|
X002548
Not Just a Number
15586 Posts |
|
PC-fre@k
Starting Member
5 Posts |
Posted - 2004-04-14 : 13:22:32
|
Hehe, well I'll ask out there. Thanks for the help so far and for the link! |
 |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-14 : 13:31:52
|
This is less of a technical problem, and more of an interface design problem....Who's going to interogate 4500 lines of data?Brett8-) |
 |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-15 : 19:57:04
|
quote: Originally posted by X002548 Never been there...what the hell is PHP?
Wow, Brett, you're leading a sheltered life, aren't you? PHP is what all those Microsoft-haters use in place of ASP. PHP runs on Apache.And by the way, I don't think was ever made completely clear, but the SQL_BIG_SELECTS OPTION is a mySQL setting.And PC-fre@k, if you're still reading here, I second (or is it third?) the idea that there should probably be a WHERE clause in your SELECT statement unless you are really, really trying to analyze every row in the table. Oh, and I'd also be surprised if you can have all those other fields in the SELECT statement and then only GROUP BY the one field (p.id). In SQL Server, every field listed in the SELECT list has to either be included in the GROUP BY or have an aggregate function run on it.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
PC-fre@k
Starting Member
5 Posts |
Posted - 2004-04-16 : 02:49:32
|
I'm stil reading here :D (I get emails)I do not get it 100%, but I do think i get what you want to tell me.it are not 4500 at once, all shoots are devived in parts of 300 or 400 that is what P.ID is in this query.I will link this page to the owner of that script, he may get more out if it then I get :)Maybe I will get some O'reily book ;) It is intresting...Thanks |
 |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-16 : 10:27:46
|
quote: Originally posted by AjarnMark
quote: Originally posted by X002548 Never been there...what the hell is PHP?
Wow, Brett, you're leading a sheltered life, aren't you?[/url]
It's lonely up here on the mainframe And who wants to be a front end developer anyway...it should be all presentation layer....All the code should be on the box....What do I care if the control is blue or pink....With that said...I just finished a phone call just about that, and figured I'm lacking and should probably set something up at home and start learning asp.net or something...Maybe build a margarita site...wait, that's been done...http://www.radiomargaritaville.comBrett8-) |
 |
|
|