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] mySQL table joins are slow, need rebuild?
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]
Re: [PHP] mySQL table joins are slow, need rebuild?
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]
Re: [PHP] mySQL table joins are slow, need rebuild?
On Tue, 27 Feb 2001 15:04:09 -0800, Joe Stump ([EMAIL PROTECTED]) 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) true, but even more important than this is to make sure there's an index on both fields. without that it will crawl. also if the 'order by' is causing problems you might want to up the tmp_table_size in my.cnf, and very important is to not let the partition that tmp is on get too full. - Mark -- 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]
