Joing a large table to a small table

MySQL & MariaDB Other MySQL® Questions
1

CREATE TABLE phones ( phone bigint(20) NOT NULL default ‘0’, consumer char(150) NOT NULL default ‘’, business char(150) NOT NULL default ‘’, address char(255) NOT NULL default ‘’, suite char(20) NOT NULL default ‘’, city char(100) NOT NULL default ‘’, state char(100) NOT NULL default ‘’, zip char(5) NOT NULL default ‘’, hoh_age smallint(5) NOT NULL default ‘0’, name_age smallint(5) NOT NULL default ‘0’, pers_in_hh smallint(5) NOT NULL default ‘0’, own_rent smallint(5) NOT NULL default ‘0’, income char(10) NOT NULL default ‘’, dwell_type smallint(5) NOT NULL default ‘0’, bankcard char(10) NOT NULL default ‘’, mari_stat smallint(5) NOT NULL default ‘0’, no_adults smallint(5) NOT NULL default ‘0’, pres_child smallint(5) NOT NULL default ‘0’, len_res char(5) NOT NULL default ‘’, income_ind char(5) NOT NULL default ‘’, misc_cc char(5) NOT NULL default ‘’, sr_cc char(5) NOT NULL default ‘’, ss_cc char(5) NOT NULL default ‘’, ur_cc char(5) NOT NULL default ‘’, us_cc char(5) NOT NULL default ‘’, og_cc char(5) NOT NULL default ‘’, fc_cc char(5) NOT NULL default ‘’, te_cc char(5) NOT NULL default ‘’, PRIMARY KEY (phone)) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO phones (phone, consumer, business, address, suite, city, state, zip, hoh_age, name_age, pers_in_hh, own_rent, income, dwell_type, bankcard, mari_stat, no_adults, pres_child, len_res, income_ind, misc_cc, sr_cc, ss_cc, ur_cc, us_cc, og_cc, fc_cc, te_cc) VALUES(5709999999, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(8060034814, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(4021204570, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(7150000000, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(8639461519, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(2130000000, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(5031504636, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(8060030006, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(5050001004, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’),(2816853641, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, 0, 0, 0, ‘’, 0, ‘’, 0, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ’ \r’);

CREATE TABLE paetec ( Bill_Prnt_Sub_ID bigint(20) NOT NULL default ‘0’, Sub_Id bigint(20) NOT NULL default ‘0’, CT_Num bigint(20) NOT NULL default ‘0’, Trans_Id bigint(20) NOT NULL default ‘0’, Trans_Dttm char(50) NOT NULL default ‘’, Trans_Typ bigint(20) NOT NULL default ‘0’, Trans_Cls char(5) NOT NULL default ‘’, Srvc_Typ int(11) NOT NULL default ‘0’, Feat_Typ int(11) NOT NULL default ‘0’, Acct_Cd int(11) NOT NULL default ‘0’, CalledNum bigint(20) NOT NULL default ‘0’, CalledCitySt char(255) NOT NULL default ‘’, Duration char(20) NOT NULL, Amount char(20) NOT NULL, Orig_Meth char(5) NOT NULL default ‘’, From_Num bigint(20) NOT NULL default ‘0’, From_City char(100) NOT NULL default ‘’, From_State char(5) NOT NULL default ‘’, To_Num bigint(20) NOT NULL default ‘0’, To_City char(100) NOT NULL default ‘’, To_State char(5) NOT NULL default ‘’, Rt_Pln_Cd char(10) NOT NULL default ‘’, From_Lata int(11) NOT NULL default ‘0’, To_Lata int(11) NOT NULL default ‘0’, LMS_Zone char(50) NOT NULL default ‘’, Orig_LRN char(50) NOT NULL default ‘’, Term_LRN char(50) NOT NULL default ‘’, From_Rate_Center char(50) NOT NULL default ‘’, To_Rate_Center char(50) NOT NULL default ‘’, KEY CalledNum (CalledNum)) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO paetec (Bill_Prnt_Sub_ID, Sub_Id, CT_Num, Trans_Id, Trans_Dttm, Trans_Typ, Trans_Cls, Srvc_Typ, Feat_Typ, Acct_Cd, CalledNum, CalledCitySt, Duration, Amount, Orig_Meth, From_Num, From_City, From_State, To_Num, To_City, To_State, Rt_Pln_Cd, From_Lata, To_Lata, LMS_Zone, Orig_LRN, Term_LRN, From_Rate_Center, To_Rate_Center) VALUES(2592494, 2592494, 8002062278, 511668076, ‘06/29/2007 11:01:03’, 100425206, ‘R’, 4, 10, 0, 3031234567, ‘Not Avail .CO’, ‘0.700000’, ‘0.027900’, ‘N’, 3031234567, ‘Not Avail’, ‘CO’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘656C’, 656, 732, ‘’, ‘’, ‘’, ‘DIR ASST’, ‘LA JOLLA\r’),(2592494, 2592494, 8002062278, 511668159, ‘06/29/2007 11:01:50’, 100425206, ‘R’, 4, 10, 0, 3031234567, ‘Not Avail .CO’, ‘14.300000’, ‘0.570600’, ‘N’, 3031234567, ‘Not Avail’, ‘CO’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘656C’, 656, 732, ‘’, ‘’, ‘’, ‘DIR ASST’, ‘LA JOLLA\r’),(2592494, 2592494, 8662062278, 515057416, ‘07/02/2007 19:35:57’, 100425104, ‘R’, 4, 12, 0, 9164227288, ‘SACRAMENTO.CA’, ‘1.100000’, ‘0.009000’, ‘N’, 9164227288, ‘SACRAMENTO’, ‘CA’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘726A’, 726, 732, ‘’, ‘’, ‘’, ‘SCRM MAIN’, ‘LA JOLLA\r’),(2592494, 2592494, 8662662277, 515057501, ‘07/02/2007 19:59:24’, 100425205, ‘R’, 4, 10, 0, 2482246561, ‘ROYAL OAK .MI’, ‘1.100000’, ‘0.011100’, ‘N’, 2482246561, ‘ROYAL OAK’, ‘MI’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘340B’, 340, 732, ‘’, ‘’, ‘’, ‘ROYAL OAK’, ‘LA JOLLA\r’),(2592494, 2592494, 8662662277, 515057502, ‘07/02/2007 20:00:44’, 100425205, ‘R’, 4, 10, 0, 2482246561, ‘ROYAL OAK .MI’, ‘1.200000’, ‘0.012100’, ‘N’, 2482246561, ‘ROYAL OAK’, ‘MI’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘340B’, 340, 732, ‘’, ‘’, ‘’, ‘ROYAL OAK’, ‘LA JOLLA\r’),(2592494, 2592494, 8882662678, 515059507, ‘07/02/2007 19:53:32’, 100425106, ‘R’, 4, 12, 0, 4158301278, ‘SNFC CNTRL.CA’, ‘0.700000’, ‘0.040000’, ‘N’, 4158301278, ‘SNFC CNTRL’, ‘CA’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘722C’, 722, 732, ‘’, ‘’, ‘’, ‘SNFC CNTRL’, ‘LA JOLLA\r’),(2592494, 2592494, 8002479563, 515041854, ‘07/02/2007 19:16:40’, 100425204, ‘R’, 4, 10, 0, 6155748688, ‘NASHVILLE .TN’, ‘0.500000’, ‘0.004000’, ‘N’, 6155748688, ‘NASHVILLE’, ‘TN’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘470A’, 470, 732, ‘’, ‘’, ‘’, ‘NASHVILLE’, ‘LA JOLLA\r’),(2592494, 2592494, 8004383929, 515043576, ‘07/02/2007 19:45:53’, 100425106, ‘R’, 4, 12, 0, 7606699845, ‘HESPERIA .CA’, ‘0.500000’, ‘0.026800’, ‘N’, 7606699845, ‘HESPERIA’, ‘CA’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘730C’, 730, 732, ‘’, ‘’, ‘’, ‘VTVL HSPR’, ‘LA JOLLA\r’),(2592494, 2592494, 8662992278, 514623838, ‘07/02/2007 18:08:51’, 100425204, ‘R’, 4, 10, 0, 2488445483, ‘AUBURN HTS.MI’, ‘0.600000’, ‘0.005000’, ‘N’, 2488445483, ‘AUBURN HTS’, ‘MI’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘340A’, 340, 732, ‘’, ‘’, ‘’, ‘AUBURN HTS’, ‘LA JOLLA\r’),(2592494, 2592494, 8772662227, 514627671, ‘07/02/2007 17:55:11’, 100425205, ‘R’, 4, 10, 0, 3607940331, ‘MONROE .WA’, ‘0.500000’, ‘0.005900’, ‘N’, 3607940331, ‘MONROE’, ‘WA’, 8589640003, ‘LA JOLLA’, ‘CA’, ‘674B’, 674, 732, ‘’, ‘’, ‘’, ‘SNOHOMISH’, ‘LA JOLLA\r’);

Query:

SELECT paetec.Bill_Prnt_Sub_IDFROM phonesLEFT JOIN paetec ON (phones.phone = paetec.CalledNum)

Paetec table has about 3mln rows
Data 6,630 MiB
Index 37,880 KiB
Row size 2,574 B

Phone table has about 100 000 rows
Data 251 k KiB
Index 1,650 KiB
Row size 2,592 B

When a join is done the result set size is about 260 000 records.

Server configuration:

key_buffer = 16Kmax_allowed_packet = 1Mtable_cache = 4sort_buffer_size = 64Kread_buffer_size = 16Mread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 64K

The problem:

The query is extremely slow (takes about 15-20 minutes) when any field from a paetec table is added to select.

2
[B]araki wrote on Thu, 13 December 2007 04:15[/B]

Server configuration:

key_buffer = 16K

replace it with

key_buffer_size = 64M

and say how it will affect this query performance.

3

And why are all your other variable values so small.

It definitely looks like you are using the my-small.cnf template setup.
Which is even smaller than the default values.

Depending on your amount of RAM I suggest that you should at least try settings somewhere between the my-medium.cnf and my-large.cnf settings.