StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSpeeding up multi-table joins with MySQL
text
Body
copied!<p>I have 3 tables in which I'm trying to preform joins on, and inserting the resulting data into another table. The query is taking anywhere between 15-30 mins depending on the dataset. The tables I'm selecting from and joining on are at least 25k records each but will quickly grow to be 500k+.</p> <p>I tried adding indexes on the fields but still isn't helping that much. Are there any other things I can try or are joins on this scale just going to take this long?</p> <p>Here is the query I'm trying to perform:</p> <pre><code>INSERT INTO audience.topitem (runs_id, total_training_count, item, standard_index_value, significance, seed_count, nonseed_count, prod, model_type, level_1, level_2, level_3, level_4, level_5) SELECT 5, seed_count + nonseed_count AS total_training_count, ii.item, standard_index_value, NULL, seed_count, nonseed_count, standard_index_value * seed_count AS prod, 'site', topic_L1, topic_L2, topic_L3, topic_L4, topic_L5 FROM audience.item_indexes ii LEFT JOIN audience.usercounts uc ON ii.item = uc.item AND ii.runs_id = uc.runs_id LEFT JOIN categorization.categorization at on ii.item = at.url WHERE ii.runs_id = 5 </code></pre> <p>Table: audience.item_indexes </p> <pre><code>CREATE TABLE `item_indexes` ( `item` varchar(1024) DEFAULT NULL, `standard_index_value` float DEFAULT NULL, `runs_id` int(11) DEFAULT NULL, `model_type` enum('site','term','combo') DEFAULT NULL, KEY `item_idx` (`item`(333)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre> <p>Table: audience.usercounts</p> <pre><code>CREATE TABLE `usercounts` ( `item` varchar(1024) DEFAULT NULL, `seed_count` int(11) DEFAULT NULL, `nonseed_count` int(11) DEFAULT NULL, `significance` float(19,6) DEFAULT NULL, `runs_id` int(11) DEFAULT NULL, `model_type` enum('site','term','combo') DEFAULT NULL, KEY `item_idx` (`item`(333)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre> <p>Table: audience.topitem</p> <pre><code>CREATE TABLE `topitem` ( `id` int(11) NOT NULL AUTO_INCREMENT, `total_training_count` int(11) DEFAULT NULL, `item` varchar(1024) DEFAULT NULL, `standard_index_value` float(19,6) DEFAULT NULL, `significance` float(19,6) DEFAULT NULL, `seed_count` int(11) DEFAULT NULL, `nonseed_count` int(11) DEFAULT NULL, `prod` float(19,6) DEFAULT NULL, `cat_type` varchar(32) DEFAULT NULL, `cat_level` int(11) DEFAULT NULL, `conf` decimal(19,9) DEFAULT NULL, `level_1` varchar(64) DEFAULT NULL, `level_2` varchar(64) DEFAULT NULL, `level_3` varchar(64) DEFAULT NULL, `level_4` varchar(64) DEFAULT NULL, `level_5` varchar(64) DEFAULT NULL, `runs_id` int(11) DEFAULT NULL, `model_type` enum('site','term','combo') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=825 DEFAULT CHARSET=utf8; </code></pre> <p>Table: categorization.categorization</p> <pre><code>CREATE TABLE `AT_categorization` ( `url` varchar(760) NOT NULL , `language` varchar(10) DEFAULT NULL, `category` text, `entity` text, `source` varchar(255) DEFAULT NULL, `topic_L1` varchar(45) NOT NULL DEFAULT '', `topic_L2` varchar(45) NOT NULL DEFAULT '', `topic_L3` varchar(45) NOT NULL DEFAULT '', `topic_L4` varchar(45) NOT NULL DEFAULT '', `topic_L5` varchar(45) NOT NULL DEFAULT '', `last_refreshed` datetime DEFAULT NULL, PRIMARY KEY (`url`,`topic_L1`,`topic_L2`,`topic_L3`,`topic_L4`,`topic_L5`), UNIQUE KEY `inx_url` (`url`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload