MySQL Forums
Forum List  »  Performance

Re: Optimizing a query to out-perform MS Access (easy, right?)
Posted by: David Skalinder
Date: October 07, 2005 09:13AM

> SELECT count(r.r_id) FROM r INNER JOIN q ON r.q_id
> = q.q_id INNER JOIN a ON r.a_id=a.a_id;

I've attached the SHOW TABLE STATUS outputs for each table below. As you can see, they're hardly heavy hitters -- a and q are teensy and r only has a few hundred K rows...

I didn't try running OPTIMIZE before because these tables have only ever been updated once so I didn't think it would help much; but after running it on all three tables, it did knock off .3 seconds. So I'm down to 7.03 at the moment, but still a long way to go...

I did notice a couple of things after my earlier post:

1.) I tried running a bunch of SHOW PROCESSLISTs while running the query to see what it was doing during the lag time. It seems to be spending most of its time in the 'Sending data' state. However, I'm not sure where to go from there, as there is surprisingly little documentation on SHOW PROCESSLIST (that I can find!) and hardly any on 'Sending data'. It makes me wonder if there is a slow connection somewhere, but everything I'm doing is local and I'm the only user, so I'm not sure where it could be...

2.) After closing the client, letting the server sit idle overnight, and trying the query again, query time jumps to around 20 seconds for the first time the query is run, then back down to 7 sec. It also does this if I restart the machine. Otherwise, I can't get it to replicate that slow run. FLUSH TABLES and RESET QUERY CACHE don't affect it; neither does restarting the server(!). Just restarting the machine. Huh? Not sure if this has anything to do with that query's performance, but it's got me stumped.

...

3.) Without really having any good reason, I just now tried adding STRAIGHT_JOIN after SELECT. And the time dropped! To about 4.5 sec. When I switch the order of the INNER JOINS, it drops again, to 3.5 sec. This makes sense, sort of, when I look at the EXPLAINs from before and after (the new one is below): because it had saved the big table for later, it had to do an extra ref when it got there instead of just one eq_ref for each of the two small ones.

This drop should be great news, but it actually worries me, for two reasons:
a.) I've still got a few seconds to go, and
b.) The optimizer was getting a pretty simple join order blatantly wrong. STRAIGHT_JOIN is an easy fix with this query, but I shudder to think about making sure that every join in each of my 100-odd 30-join queries is in the optimal order (hmm, so that's 100*30!... brr-rrh).

Well, time for me to dive back into the man and find out more about how the optimizer works. Sorry if these are head-hit-keyboard questions, I am very new to the MySQL game...

Anybody have any ideas of how to knock off another 2 or 3 seconds? Or, better yet, how to lose the STRAIGHT_JOIN and knock off 6?



-------------Table info and the latest EXPLAIN------------------

mysql> SHOW TABLE STATUS LIKE 'r'\G
*************************** 1. row ***************************
Name: r
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 387048
Avg_row_length: 28
Data_length: 11155900
Max_data_length: 4294967295
Index_length: 14733312
Data_free: 0
Auto_increment: NULL
Create_time: 2005-10-03 21:45:11
Update_time: 2005-10-03 21:45:28
Check_time: 2005-10-07 12:42:20
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.05 sec)

mysql> SHOW TABLE STATUS LIKE 'q'\G
*************************** 1. row ***************************
Name: q
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 413
Avg_row_length: 104
Data_length: 43304
Max_data_length: 4294967295
Index_length: 14336
Data_free: 0
Auto_increment: NULL
Create_time: 2005-09-27 21:33:21
Update_time: 2005-09-27 21:46:39
Check_time: 2005-10-07 12:42:58
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)

mysql> SHOW TABLE STATUS LIKE 'a'\G
*************************** 1. row ***************************
Name: a
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 249
Avg_row_length: 59
Data_length: 14860
Max_data_length: 4294967295
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2005-10-03 21:46:15
Update_time: 2005-10-03 21:46:15
Check_time: 2005-10-07 12:42:49
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT /*! STRAIGHT_JOIN */ count(r.r_id)
-> FROM r
-> INNER JOIN a
-> ON r.a_id = a.a_id
-> INNER JOIN q
-> ON r.q_id = q.q_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: q_id,a_id
key: NULL
key_len: NULL
ref: NULL
rows: 387048
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: f05.r.a_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: q
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: f05.r.q_id
rows: 1
Extra: Using index
3 rows in set (0.34 sec)

mysql> SELECT /*! STRAIGHT_JOIN */ count(r.r_id)
-> FROM r
-> INNER JOIN a
-> ON r.a_id = a.a_id
-> INNER JOIN q
-> ON r.q_id = q.q_id;
+---------------------------------------+
| count(r.r_id) |
+---------------------------------------+
| 299016 |
+---------------------------------------+
1 row in set (3.58 sec)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.