I am trying to make a join in two tables in MySQL and the query will run for a minute or two before I run out of memory without getting any results. I am far from a database expert, so I’m not sure that I didn’t write my queries well if my MySQL settings are incorrect or I really have to do something else with my query. The FYI database is located locally on my machine.
I have a large table (~ 2 million records), where one of the columns is the identifier in a small table (~ 3000 records). In case this matters, the identifier is not unique in the large table, but unique in the small table. I tried various variations of the following query, but nothing works:
SELECT big_table.*,
small_table.col
FROM big_table
left outer join small_table on (big_table.small_id = small_table.id)
I am doing a large analysis of data that requires only 2 million rows, although not necessarily in a single query. Here are the results of my "show create table":
'big_table', 'CREATE TABLE 'big_table' (
'BIG_ID_1', varchar(12) NOT NULL,
'BIG_ID_2', int(100) NOT NULL,
'SMALL_ID' varchar(8) DEFAULT NULL,
'TYPICAL_OTHER_COLUMN' varchar(3) DEFAULT NULL,
...
PRIMARY KEY ('BIG_ID_1', 'BIG_ID_2')
) ENGINE=MyISAM DEFAULT CHARSET=latin1'
'small_table', 'CREATE TABLE `small_table` (
`id`, varchar(8) NOT NULL DEFAULT '''',
`col`, varchar(1) DEFAULT NULL,
...
PRIMARY KEY (`id`),
KEY `inx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'
Here is the result of an “explanation” for one of my potential queries:
id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE big_table ALL NULL NULL NULL NULL 1962193
1 SIMPLE small_table eq_ref PRIMARY, inx_id PRIMARY 10 db_name.big_table.SMALL_ID 1