MySQL Forums
Forum List  »  Optimizer & Parser

Re: Long query execution, about 1 hr
Posted by: eloe lelo
Date: February 15, 2011 03:04PM

CREATE TABLE `kontakt` (
  `id` int(11) NOT NULL,
  `imie_nazwisko` varchar(200) DEFAULT NULL,
  `makroregion` varchar(45) DEFAULT NULL,
  `region` varchar(45) DEFAULT NULL,
  `oddzial` varchar(200) DEFAULT NULL,
  `akcja` int(11) DEFAULT NULL,
  `akcja_monitorowana` varchar(3) DEFAULT NULL,
  `grupa` varchar(5) DEFAULT NULL,
  `akcja_data_rozpoczecia` date DEFAULT NULL,
  `akcja_data_zakonczenia` date DEFAULT NULL,
  `status_opis` varchar(200) DEFAULT NULL,
  `rezultat_opis` varchar(200) DEFAULT NULL,
  `definicja_sprzedazy` varchar(3) DEFAULT NULL,
  `data_wgrania` date DEFAULT NULL,
  `data_zamkniecia` date DEFAULT NULL,
  `cif` int(11) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `kontakt_sws_akcja_cif` (`akcja`,`cif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `sprzedaz` (
  `cif` int(11) DEFAULT NULL,
  `makroregion` varchar(45) DEFAULT NULL,
  `region` varchar(45) DEFAULT NULL,
  `oddzial` varchar(200) DEFAULT NULL,
  `akcja` int(11) DEFAULT NULL,
  `akcja_nazwa` varchar(200) DEFAULT NULL,
  `akcja_data_rozpoczecia` date DEFAULT NULL,
  `akcja_data_zakonczenia` date DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `data_operacji` date DEFAULT NULL,
  KEY `sprzedaz_akcja_cif` (`akcja`,`cif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `segment` (
  `id` int(11) NOT NULL,
  `pion` varchar(3) DEFAULT NULL,
  `rodzaj` varchar(3) DEFAULT NULL,
  `opis` varchar(45) DEFAULT NULL,
  `opis_ext` varchar(200) DEFAULT NULL,
  `czytaj` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `akcja` (
  `id` int(11) NOT NULL,
  `nazwa` varchar(200) DEFAULT NULL,
  `rodzaj` varchar(20) DEFAULT NULL,
  `monitorowana` varchar(3) DEFAULT NULL,
  `data_rozpoczecia` date DEFAULT NULL,
  `data_zakonczenia` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

status
akcja, InnoDB, 10, Compact, 479, 171, 81920, 0, 0, 1356857344, , 2011-02-13 21:49:41, , , utf8_general_ci, , , 

kontakt, InnoDB, 10, Compact, 890639, 325, 290275328, 0, 29949952, 1356857344, , 2011-02-13 21:49:42, , , utf8_general_ci, , , 

sprzedaz, InnoDB, 10, Compact, 142719, 246, 35192832, 0, 5783552, 1356857344, , 2011-02-13 21:49:42, , , utf8_general_ci, , , 


segment, InnoDB, 10, Compact, 108, 151, 16384, 0, 0, 1356857344, , 2011-02-13 21:49:42, , , utf8_general_ci, , ,

explain

1, SIMPLE, kontakt, ALL, kontakt_akcja_cif, , , , 890639, Using where; Using temporary; Using filesort
1, SIMPLE, akcja, eq_ref, PRIMARY, PRIMARY, 4, sws.kontakt.akcja, 1, Using where
1, SIMPLE, cif, eq_ref, PRIMARY, PRIMARY, 8, sws.kontakt.cif, 1, 
1, SIMPLE, segment, eq_ref, PRIMARY, PRIMARY, 4, misc.cif.segment, 1, 
1, SIMPLE, sprzedaz, ref, sprzedaz_akcja_cif, sprzedaz_akcja_cif, 10, sws.kontakt.akcja,sws.kontakt.cif, 1,

buffer
bulk_insert_buffer_size, 8388608
innodb_buffer_pool_instances, 1
innodb_buffer_pool_size, 49283072
innodb_change_buffering, all
innodb_log_buffer_size, 1048576
join_buffer_size, 131072
key_buffer_size, 26214400
myisam_sort_buffer_size, 36700160
net_buffer_length, 16384
preload_buffer_size, 32768
read_buffer_size, 65536
read_rnd_buffer_size, 262144
sort_buffer_size, 262144
sql_buffer_result, OFF

Thanks for help.

Options: ReplyQuote


Subject
Views
Written By
Posted
3546
February 13, 2011 10:07AM
1222
February 14, 2011 07:00PM
Re: Long query execution, about 1 hr
1211
February 15, 2011 03:04PM
1262
February 16, 2011 12:17AM


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.