CODEWITHSUNDEEP
mysqlperformance
Angelin Calu
Angelin Calu

Reputation: 1915

Mysql Query takes more than 300 seconds

I have two MYSQL tables, A and B. Table A has 44,902 rows and table B has 109,583 rows. I would like to compare two columns from the two tables and return the rows from table A where it finds a match. My, unsuccessful queries are:

SELECT pool.domain_name FROM `pool`, `en_dict` WHERE pool.domain_string = en_dict.word

and another variant:

SELECT a.domain_name FROM `pool` as a inner join en_dict as b  on a.domain_string = b.word

both solutions falied returning any values under 300 seconds.

What should I do to reduce the time for finding the matches??

P.S. I have tried adding a LIMIT at the end of the queries and managed to display 10 results in 245 seconds.

Edit: My tables structures are as follows :

--
-- Table structure for table `en_dict`
--

CREATE TABLE `en_dict` (
  `word_id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `word` varchar(35) NOT NULL,
  PRIMARY KEY (`word_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=109584 ;

-- --------------------------------------------------------

--
-- Table structure for table `pool`
--

CREATE TABLE `pool` (
  `domain_id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(100) NOT NULL,
  `domain_tld` varchar(10) NOT NULL,
  `domain_string` varchar(90) NOT NULL,
  `domain_lenght` int(2) NOT NULL,
  `domain_expiretime` date NOT NULL,
  PRIMARY KEY (`domain_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=44903 ;

Upvotes: 1

Views: 336

Answers (1)

Mischa
Mischa

Reputation: 43298

Try adding an index on the relevant columns of your tables:

ALTER TABLE `pool` ADD INDEX `domain_string_idx` (`domain_string`);
ALTER TABLE `en_dict` ADD INDEX `word_idx` (`word`);

Upvotes: 3

Related Questions