performance - Improve speed on MySQL JOIN with big data -

below 2 tables want fetch data (note these dummy databases don't pay attention data i'm pulling)

> describe bigdata;  +----------------+----------------------+------+-----+---------+-------+ | field          | type                 | null | key | default | | +----------------+----------------------+------+-----+---------+-------+ | galaxy         | int(2)               | no   | pri | 0       |       | | system         | int(3)               | no   | pri | 0       |       | | planet         | int(2)               | no   | pri | 0       |       | | ogame_playerid | int(11) unsigned     | no   | mul | 0       |       | | moon           | enum('true','false') | no   |     | false   |       | | moonsize       | smallint(5) unsigned | no   |     | 0       |       | | metal          | int(10) unsigned     | no   |     | 0       |       | | crystal        | int(10) unsigned     | no   |     | 0       |       | | planetname     | varchar(40)          | no   |     |         |       | +----------------+----------------------+------+-----+---------+-------+ 

also create table @drew requested

create table `bigdata` (     `galaxy` int(2) not null default '0',     `system` int(3) not null default '0',     `planet` int(2) not null default '0',     `ogame_playerid` int(11) unsigned not null default '0',     `moon` enum('true','false') not null default 'false',     `moonsize` smallint(5) unsigned not null default '0',     `metal` int(10) unsigned not null default '0',     `crystal` int(10) unsigned not null default '0',     `planetname` varchar(40) not null default '',     primary key (`galaxy`, `system`, `planet`),     index `player_id` (`ogame_playerid`) ) collate='utf8_general_ci' engine=myisam; 

and second table (note there more userid's)

select * smalldata userid = 1; +----+--------+----------------+---------------------+ | id | userid | ip             | logintime           | +----+--------+----------------+---------------------+ |  1 |      1 | 127.0.0.1      | 2016-02-25 13:50:59 | |  2 |      1 | ::1            | 2016-02-29 23:22:18 | |  3 |      1 | 127.0.0.1      | 2016-03-14 22:52:22 | |  4 |      1 | 127.0.0.1      | 2016-03-22 23:27:02 | +----+--------+----------------+---------------------+ 

my query below

select smalldata.id, sum(bigdata.planet)  smalldata left join bigdata on smalldata.id = bigdata.galaxy  smalldata.userid = 1  group smalldata.id; 

my concern if run such query automatize selection on 4 smalldata's ids @ once, takes around 10 seconds complete. however, if skip left join , execute 4 invidual queries on bigdata, "hardcoding" where galaxy = 1(or 2,3,4 respectively), takes around 0.05 second each.

i wonder why happens. presume might left join utilizes lot of data columns in bigdata i'm not using (which i'm not selecting) such moon, moonsize etc. might perhaps join time consuming in fact perform 4 selections bigdata without joining tables.

am misusing join here?

try writing code this:

select smalldata.id,        (select sum(bigdata.planet)          bigdata          smalldata.id = bigdata.galaxy         ) smalldata smalldata.userid = 1 ; 

be sure have index on smalldata(userid, id). based on description, seem have right index on bigdata (galaxy should first key in index , planet should in index well).