Re: 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: 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 -------------------------------------------------------------------------------