How to join two huge tables in MySQL/MariaDB?

user_2037411 0
Viewed 170

I have two tables kw with 250000 records and t_n with 100000 records.
I joined both tables to get collective results in following query:

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw, t_n 
WHERE kw.id = t_n.keyword_id
  AND kw.word LIKE '1%' 
GROUP BY t_n.translation 
LIMIT 10

I also tried following simple join as an alternate of above query:

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw 
INNER JOIN t_n
    ON kw.id = t_n.keyword_id
   AND kw.word LIKE '1%' 
GROUP BY t_n.translation 
LIMIT 10

But both queries took about 60 to 150 seconds depending on the system resources of memory and processing. This execution timeout is unbearable for extending database and increasing multiple users.
Is there any efficient way to join two huge tables?

UPDATE
This query is for JQuery UI Autocomplete. And I used GROUP BY Clause to get distinct values values. Is there any other suitable solution to get distinct values.

Tables Structure

--
-- Table structure for table `kw`
--

CREATE TABLE `kw` (
  `id` int(32) NOT NULL,
  `word` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pos` varchar(12) NOT NULL,
  `definition` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_n`
--

CREATE TABLE `t_n` (
  `id` int(16) NOT NULL,
  `keyword_id` int(16) NOT NULL,
  `translation` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_on` datetime NOT NULL,
  `user_id` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `kw`
--
ALTER TABLE `kw`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `t_n`
--
ALTER TABLE `t_n`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `kw`
--
ALTER TABLE `kw`
  MODIFY `id` int(32) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `t_n`
--
ALTER TABLE `t_n`
  MODIFY `id` int(16) NOT NULL AUTO_INCREMENT;
COMMIT;
mariadb join mysql sql
3 Answers

I am guessing that you want:

SELECT kw.id as kw_id, kw.word as kw_word, t_n.translation as t_n_translation
FROM kw INNER JOIN
     t_n
     ON kw.id = t_n.keyword_id AND kw.word LIKE '1%' ;

For this query, you want an index on kw(word, id) and t_n(keyword_id):

create index idx_kw_word_id on kw(word, id);
create index idx_t_n_keyword_id on t_n(keyword_id);
user_11440350

The proper way is to use JOIN ON to say how the tables are related and WHERE for filtering:

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw 
INNER JOIN t_n
    ON kw.id = t_n.keyword_id
WHERE kw.word LIKE '1%' 
ORDER BY t_n.translation 
LIMIT 10

It is unclear that the GROUP BY is necessary or proper. Please explain its purpose, and tell us what happens without it. A LIMIT without an ORDER BY is rarely meaningful. Perhaps you meant ORDER BY instead of GROUP BY? (So I made that change.)

Assuming you don't have any TEXT columns, these indexes should help:

kw:   INDEX(word, id)
t_n:  INDEX(keyword_id, translation)

These indexes are "composite", but that is not very relevant, and "covering", which gives a performance boost.

If I can see SHOW CREATE TABLE, there may be further improvements, such as modifying the PRIMARY KEY.

More

int(32) -- the (32) means nothing. INT is always a 32 bit (4-byte) number; SMALLINT is a 16-bit (2-byte) number.

It seems like keyword_id would be unique in t_n. If so, make it the PRIMARY KEY and completely get rid if id.

At that point, it seems like the two tables could merge into one? There are lots of distinct words, each one has a single translation? But maybe not, since I see that the two tables are different sizes. Please explain the schema.

user_17668310

I can to advice next solution:

  1. add index:

    CREATE INDEX keyword_id_ix on t_n(keyword_id);

  2. filter big table using LIKE:

    SELECT * FROM kw WHERE  kw.word LIKE '1%'

  3. Combine result with second table using JOIN:

    SELECT
    kw.id as kw_id, kw.word as kw_word, t_n.translation as t_n_translation
FROM (
    SELECT * FROM kw WHERE  kw.word LIKE '1%'
) kw 
INNER JOIN t_n ON kw.id = t_n.keyword_id;

SQL fiddle here

user_33993560
Related