This is an index problem. Your tables don't contain any indices except on PKs. This can't work, given the number of joins and table sizes. Read the doc about indices. Stefan
Am Monday 12 July 2004 09:55 schrieb Jeyabalan Murugesan Sankarasubramanian: > Hi All, > > I migrated the data from Oracle to MySQL.The following query works fine > with Oracle whereas in MySql its hanging. > > SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct > segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers > where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id > = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = > pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, > st_caty.notes order by st_caty.name > > Following are the table structures with Row count in each table. > > > CREATE TABLE segs( > s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, > c_id INT(12) NULL , > text VARCHAR(255) NULL, > lookup VARCHAR(255) NULL, > in_id INT(12) NULL, > prr_d VARCHAR(12) NULL, > nxt_d VARCHAR(12) NULL, > descn VARCHAR(255) NULL, > notes VARCHAR(255) NULL, > s_st_id INT(12) NULL, > versn FLOAT(10,4) NULL, > mesg_type VARCHAR(50) NULL > ); > Row count 34700 > > CREATE TABLE caty( > c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, > name VARCHAR(255) NULL, > notes VARCHAR(255) NULL, > version FLOAT(10,4) > ); > > Row count 281 > > CREATE TABLE st_mbers( > st_id INT(12) NULL, > c_id INT(12) NULL, > version FLOAT(10,4) NULL, > st_mber_id INT(12) NOT NULL > ); > Row count 1362 > > CREATE TABLE t_mbers( > t_id INT(12) NOT NULL, > st_id INT(12) NULL, > seq_nbr INT(12) NULL, > version FLOAT(10,4) NULL, > t_mber_id INT(12) NOT NULL > ); > > Row count 1260 > > CREATE TABLE p_mbers ( > p_id INT(12) NOT NULL , > t_id INT(12) NULL , > seq_nbr INT(12) NULL, > notes TEXT NULL, > version FLOAT(10,4) NULL, > p_mber_id INT(12) NOT NULL > ); > > Row Count 2198 > CREATE TABLE pr_mbers( > pr_id INT(12) NULL, > p_id INT(12) NULL, > seq_nbr INT(12) NULL, > pr_mbr_id INT(12) NOT NULL > ); > > Row Count 294 > > > > Help me in solving this hanging issue. I tried the same with both Linux and > Windows XP but without any success. I tried with one record in each table > mentioned above and its working fine. Kindly guide me in this regard. > > Thanks in advance. > > > regards > msjeyabalan > > > ****************************************** > CONFIDENTIAL INFORMATION > ****************************************** > > This e-mail transmission and any attachments may contain confidential > information belonging to the sender. The information is intended solely > for the use of the individual(s) or entities addressed. If you are not the > intended recipient, you are hereby notified that any copying, disclosing, > distributing, or use of this e-mail and/or attachment is strictly > prohibited. If you received this transmission in error please notify the > sender immediately and delete the message and all attachments. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) ZÃlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
