Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel