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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
