mysql - MariaDB copy table query runs quicker than original table -

scenario is:

maria db on cluster

i have table, updated twice day, when query takes 6-7 seconds, (it not big table on 100k of rows), when make copy of table , run same query comes 0.7 seconds.

now tried optimize original table no luck. there indexes , btree. have feeling when copy table index straighten speak.

anyone have idea? tried few suggestions no luck.

(copied comment)

select  sql_no_cache         value1, tbl1.c, src,         group_concat( distinct round( tbl1.price, 4 )             order  tbl1.purdate desc, tbl1.acttime desc separator ' - '             ) purchase,         concat_ws (" ", curdef.country , curdef.currname) defin      tbl1     left join  curdef on tbl1.curr = curdef.curr      purdate between date_sub("2015-07-06",interval 1 day) , "2015-07-06"       ,  curdef.curr_def not null       ,  base = "usd"     group  curr,curdef.curr_id     order  tbl1.curr asc, tbl1.feeddate desc; 

here explain original table

select type =   simple                                                                              table       =   curdef                                                                            type        =                                                                                               possible keys = null                                                                                key           = null                                                                            key len     =   null                                                                            ref         =   null                                                                                    rows        =   195                                                                                   =   using where; using temporary; using filesort 

the join table

select type =   simple   table       =   tble1    type        =   ref                  possible keys = curr,base,feeddate,src   key           = curr key len     =   257      ref         =   curdef.curr  rows        =   343         =   using index condition; using  /*****************************************************************************/ 

and here 2nd explain table copied table

select type =   simple   table       =   curdef   type        =                    possible keys = null     key           = null key len     =   null ref         =   null         rows        =   195         =   using where; using temporary; using filesort  /*****************************************************************************/  select type =   simple   table       =   tbl1_test_01     type        =   range    possible keys = curr,base,feeddate,src   key           = feeddate     key len     =   3        ref         =   null rows        =   1462            = using index condition; using where; using join buffer (flat, bnl join) 

/*****************************************************************************/

create table `tbl1` (   `id` int(11) not null auto_increment,   `purchase` varchar(255) not null,   `rate` double not null,   `feeddate` date not null default '0000-00-00',   `base` varchar(255) not null default 'usd',   `acttime` timestamp not null default current_timestamp on update current_timestamp,   `src` varchar(255) not null,   `time` varchar(255) default null,   `actflag` char(255) not null,   primary key (`id`),   key `curr` (`curr`),   key `base` (`base`),   key `feeddate` (`feeddate`),   key `src` (`src`) ) engine=innodb auto_increment=1911063 default charset=latin1 

/*****************************************************************************/

create table `curdef` (   `curr_id` int(11) not null auto_increment,   `curr` varchar(3) not null,   `curr_def` varchar(255) not null,   `country` varchar(60) default null,   `currname` varchar(60) default null,   `region` varchar(45) default null,   `country_code` varchar(3) default null,   primary key (`curr_id`) ) engine=innodb auto_increment=214 default charset=latin1' 

/*****************************************************************************/

create table `tbl1_test_01` (   `id` int(11) not null auto_increment,   `purchase` varchar(255) not null,   `rate` double not null,   `feeddate` date not null default '0000-00-00',   `base` varchar(255) not null default 'usd',   `acttime` timestamp not null default current_timestamp on update  current_timestamp,   `src` varchar(255) not null,   `time` varchar(255) default null,   `actflag` char(255) not null,   primary key (`id`),   key `curr` (`curr`),   key `base` (`base`),   key `feeddate` (`feeddate`),   key `src` (`src`) ) engine=innodb auto_increment=1911063 default charset=latin1 

so here show create table tbl1 , tbl1_test_01 join curdef

right after making copy of table, data sitting in cache. hence, runs 10 times fast.

let's check cache size. how ram? engine? value of innodb_buffer_pool_size?

please provide show create table both tables.

please qualify column names can tell table each column in.

if base , purdate in same table, composite index may speed query (before and after copying table):

index(base, purdate) 

in case need more discussion, please provide explain select ....

edit

the copy seems have more indexes. not have composite index containing both base , purdate.