Re: [PHP] mySQL table joins are slow, need rebuild?
It also seems you have a semicolon in your query, mysql_query()
specifically states not to have on at the end of your queries, so I am
guessing this may be a factor...
Steve
Joe Stump wrote:
You need to remember a few things when it comes to joins:
the joined fields must be the EXACT same definition
- example: a join on id int(9) and id int(3) will NOT be optimized
- more: a join on id char(9) and id int(9) is REALLY NOT optimized :O)
We have an accounts table with userID as the key char(15) (don't ask, it's an
old design made by a former employee) which has roughly 1.6 million rows in it.
We regularily do joins on it with other tables that have thousands of records
in less than .05 seconds.
This sounds like a table structure problem to me.
--Joe
On Tue, Feb 27, 2001 at 02:21:53PM -0800, Jason wrote:
hi,
i have a query that is comparing a table with 1235 rows with another that
has 635 rows. The query looks like this:
$res = mysql_query("select cust_info.ID, cust_info.first_name,
cust_info.last_name, cust_info.address, cust_info.datestamp from cust_info,
cust_order_info where cust_info.ID=cust_order_info.cust_id order by
$mainsort" . $order . ";");
The parse time with the join is 19 seconds. I have to do a join because
there a different methods that the user must be able to sort by. The parse
time on the cust_info table alone, with a order by is .95 seconds.
Now, we have a RPM binary of mySQL, and when performing the query, not only
is it slow, but sometimes will dump its core.
Does anyone see anything wrong with the query, or should we consider
building the source on our box.. or?
Thanks.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
---
Joe Stump, PHP Hacker, [EMAIL PROTECTED] -o)
http://www.miester.org http://www.care2.com /\\
"It's not enough to succeed. Everyone else must fail" -- Larry Ellison _\_V
---
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Weird Problem
Hi everyone,
First, let me explain I am a very knowledgable php developer. I'm not
someone trying to write their first script. I have been working on a
very large and complex problem for some time now when it suddenly
stopped working. I did not change anything in any configurations or
edit any code that would have affected how my database functionality
class would function. But, all of my connections started to bomb out.
Follows are the entire contents of a file simp.php (minus the line numbers):
1 ?
2
3 $db = mysql_connect("localhost", "web", "pw") || print "error
connecting ".mysql_error()." ".mysql_errno();
4 mysql_select_db("surveys");
5
6 $result = mysql_query("select * from attract") || print "error
querying ".mysql_error()." ".mysql_errno();
7
8 $i = 0;
9 while ($row = mysql_fetch_array(mysql_query("select * from
attract"))){ // ($i++ 5)){
10 echo $row[0]." ".$row[1]."br";
11 }
12 ?
Please note that the user web@localhost identified by pw has permissions
to select from the table attract.
Here is what I have identified so far:
A - If I use $result in mysql_fetch_array() line 9, I get the error:
Warning: Supplied argument is not a valid MySQL result resource in
simp.php3 on line 9
But if I use the statement mysql_query("select * from attract")
in its
place, the loop (although infinite) will produce expected results, ie
print out the first and second columns of the first row of the table
infinitely. So basically, I would conclude the life of the variable
result is terminiating before I can use it. Why, I don't have a clue.
B - Being tired of an infinite loop in my investigation, I added line 8
and I placed the statement ($i++ 5) inside the while loop
condition. Now, only the br and spaces from line 10 will print, not
the actual values of the $row array. The output is br br br br
br, with no values from the table attract.
This is where I stand now, completely flabberghasted and nearly insane.
Can anyone shed any light on this before I have to commit to bringing
down the webserver while I reinstall php4. (oh, btw I am running
Apache/1.3.14 (Unix) PHP/4.0.4pl1 on Red Hat 6.2 (I hate redhat, but the
bossman says its better supported than slakware. bah.))
Thank you all for your help.
steve
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
