Re: 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: php-general-unsubscribe@lists.php.net > > For additional commands, e-mail: php-general-help@lists.php.net > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > -- > > ------------------------------------------------------------------------------- > Joe Stump, PHP Hacker, joestump98@yahoo.com -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: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net