Thanks to everyone for their help, I actually managed to fix the problem by bypassing
MySQL's optimization using STRAIGHT_JOIN. Out of curiosity can anyone tell me why
MySQL failed so miserably at optimizing my query? (The two hour long query took 10s
with Straight_join).
mysql> explain select [cols]
-> from y02m07_acode_table, y02m07_acom_table, y02m07_pats_table
-> where
-> code="87901"
-> AND y02m07_pats_table.pat_id=y02m07_acom_table.pat_id
-> AND y02m07_acom_table.h_id=y02m07_acode_table.h_id
-> AND y02m07_acom_table.c_id=y02m07_acode_table.ce_id;
+--------------------+------+---------------------+------------+---------+------------------------------+----------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+--------------------+------+---------------------+------------+---------+------------------------------+----------+-------------+
| y02m07_acom_table | ALL | c,p,h | NULL | NULL | NULL
| 46893187 | |
| y02m07_acode_table | ref | c | c | 4 |
y02m07_acom_table.charge_id | 1 | Using where |
| y02m07_pats_table | ref | p_id | p_id | 9 |
y02m07_acom_table.patient_id | 1 | Using where |
+--------------------+------+---------------------+------------+---------+------------------------------+----------+-------------+
3 rows in set (0.01 sec)
But when I add Straight Join:
+--------------------+------+---------------------+------------+---------+------------------------------+--------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+--------------------+------+---------------------+------------+---------+------------------------------+--------+-------------+
| y02m07_acode_table | ALL | c | NULL | NULL | NULL
| 736010 | Using where |
| y02m07_acom_table | ref | c,p,h | c | 4 |
y02m07_acode_table.charge_id | 90 | Using where |
| y02m07_pats_table | ref | p_id | p_id | 9 |
y02m07_acom_table.patient_id | 1 | Using where |
+--------------------+------+---------------------+------------+---------+------------------------------+--------+-------------+
3 rows in set (0.00 sec)
Thanks for whatever insight you can give...
-Charlie
-----Original Message-----
From: Knepley, Jim [ <mailto:[EMAIL PROTECTED]> mailto:[EMAIL PROTECTED]
Sent: Friday, June 27, 2003 10:33 AM
To: [EMAIL PROTECTED]
Subject: RE: very long query time
I have had similar performance concerns, but on a much smaller scale.
The data was well indexed, but took far too long to query (particularly
with aggregate queries).
Check the individual row size of your table. In my case, I had a TEXT
field that would frequently be fairly long. Moving that field to another
table and indexing back resulted in a massive performance improvement. A
query that would take minutes now takes less than a second. I figured it
was a question of IO latency, and moved on.
J
-----Original Message-----
From: Maurice Coyle [ <mailto:[EMAIL PROTECTED]> mailto:[EMAIL PROTECTED]
Sent: Friday, June 27, 2003 3:35 AM
To: [EMAIL PROTECTED]
Subject: very long query time
hi all,
i have a table in my mysql database with around 66 million rows in it.
when i query this table, it takes anywhere from 3 minutes to 10 minutes
to return the results. i've tried this both from within the mysql
command line and from java programs.
Section 1.2.4 in the manual says the maximum table size is 4Gb and when
i use the show status command for this table, it says the data_length is
1,585,947,820 and the max_data_length is 4,294,967,295, so the table
size seems to be well within the limit.
The results for a query to this table can contain up to 11500 hits, so
maybe this is the problem? If there's no fix for this, does anyone know
how i can query for only the top 100 results, say?
i can't see what's wrong, can anyone shed some light on this
problem/offer the benefit of your experience in similar matters? i'd
really appreciate it if you could.
thanks,
maurice
____________________________________________________
< <http://www.incredimail.com/redir.asp?ad_id=309&lang=9>
http://www.incredimail.com/redir.asp?ad_id=309&lang=9>
IncrediMail - Email has finally evolved - Click Here
< <http://www.incredimail.com/redir.asp?ad_id=309&lang=9>
http://www.incredimail.com/redir.asp?ad_id=309&lang=9>
