Announcement

Announcement Module
Collapse
No announcement yet.

Joing a large table to a small table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Joing a large table to a small table

    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
    araki wrote on Thu, 13 December 2007 04:15


    Server configuration:


    key_buffer = 16K




    replace it with

    key_buffer_size = 64M


    and say how it will affect this query performance.

    Comment


    • #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.

      Comment

      Working...
      X