Alex Rivera | Logout

Why can't MySQL optimize this query?

Asked 2012-03-01T18:44:56.453
9

I have a query that is giving me problems and I can't understand why MySQL's query optimizer is behaving the way it is. Here is the background info:

I have 3 tables. Two are relatively small and one is large.

Table 1 (very small, 727 rows):

CREATE TABLE ipa (
ipa_id int(11) NOT NULL AUTO_INCREMENT,
ipa_code int(11) DEFAULT NULL,
ipa_name varchar(100) DEFAULT NULL,
payorcode varchar(2) DEFAULT NULL,
compid int(11) DEFAULT '2'
PRIMARY KEY (ipa_id),
KEY ipa_code (ipa_code) ) ENGINE=MyISAM

Table 2 (smallish, 59455 rows):

CREATE TABLE assign_ipa (
assignid int(11) NOT NULL AUTO_INCREMENT,
ipa_id int(11) NOT NULL,
userid int(11) NOT NULL,
username varchar(20) DEFAULT NULL,
compid int(11) DEFAULT NULL,
PayorCode char(10) DEFAULT NULL
PRIMARY KEY (assignid),
UNIQUE KEY assignid (assignid,ipa_id),
KEY ipa_id (ipa_id)
) ENGINE=MyISAM

Table 3 (large, 24,711,730 rows):

CREATE TABLE master_final (
IPA int(11) DEFAULT NULL,
MbrCt smallint(6) DEFAULT '0',
PayorCode varchar(4) DEFAULT 'WC',
KEY idx_IPA (IPA)
) ENGINE=MyISAM DEFAULT

Now for the query. I'm doing a 3-way join using the first two smaller tables to essentially subset the big table on one of it's indexed values. Basically, I get a list of IDs for a user, SJOnes and query the big file for those IDs.

mysql
Edit
Report

1 Answer

2

This is probably not a direct answer to your question, but here are few things that you can do:

  1. Run ANALYZE_TABLE ...it will update table statistics which has a great impact on what optimizer will decide to do.

  2. If you still think that joins are not in order you wish them to be (which happens in your case, and thus optimizer is not using indexes as you expect it to do), you can use STRAIGHT_JOIN ... from here: "STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order"

  3. For me, putting "where part" right into join sometimes makes a difference and speeds things up. For example, you can write:

...t1 INNER JOIN t2 ON t1.k1 = t2.k2 AND t2.k2=something...

instead of

...t1 INNER JOIN t2 ON t1.k1 = t2.k2 .... WHERE t2.k2=something...

So this is definitely not an explanation on why you have that behavior but just few hints. Query optimizer is a strange beast, but fortunately there is EXPLAIN command which can help you to trick it to behave in a way you want.

answered 2012-03-01T19:05:35.433

Your Answer