I suggest that you add more indexes to your tables. If you run an EXPLAIN
on your query, you will see that you are doing WAY too many table scans and
that is what is slowing you down. Index the columns in each table that
reference the ID values of another table. Then run your EXPLAIN again and
you should see a major difference.
Use the manual, it has great advice on optimizing queries and full
documentation of the EXPLAIN command.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Jeyabalan
Murugesan To: [EMAIL PROTECTED]
Sankarasubramania cc:
n" Fax to:
<[EMAIL PROTECTED] Subject: Sql Query Issue
xis.com>
07/12/2004 03:55
AM
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
