MariaDB copy table query runs quicker than original table

Question

Scenario is:

Maria db on a cluster

I have a table, which is updated twice a day, when i query it takes 6-7 seconds, (it is not a big table over 100k of rows), but when i make a copy of the table and run the same query it comes back 0.7 seconds.

Now i tried to optimize the original table but with no luck. There are indexes and BTree. I have a feeling that when copy a table the index is straighten so to speak.

Anyone have an idea? I tried a few suggestions with no luck.

(Copied from comment)

SELECT  SQL_NO_CACHE
        value1, tbl1.C, Src,
        GROUP_CONCAT( DISTINCT ROUND( tbl1.price, 4 )
            ORDER BY  tbl1.purdate DESC, tbl1.acttime DESC SEPARATOR ' - '
            ) AS purChase,
        CONCAT_WS (" ", curdef.country , curdef.currname) AS defin
    FROM  tbl1
    LEFT JOIN  curdef ON tbl1.curr = curdef.curr
    WHERE  purdate BETWEEN DATE_SUB("2015-07-06",INTERVAL 1 DAY) AND "2015-07-06"
      AND  curdef.curr_Def IS NOT NULL
      AND  BASE = "USD"
    GROUP BY  Curr,curdef.curr_id
    ORDER BY  tbl1.Curr ASC, tbl1.feeddate DESC;

HERE is the EXPLAIN from the original table

SELECT Type =   SIMPLE                                                                             
TAble       =   curdef                                                                           
Type        =   ALL                                                                                            
possible Keys = null                                                                               
Key           = null                                                                           
Key Len     =   null                                                                           
ref         =   null                                                                                   
rows        =   195                                                                            
Extra       =   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 

Extra       =   Using index condition; Using where

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

and here is the 2nd explain table from the copied table

SELECT Type =   SIMPLE  
TAble       =   curdef  
Type        =   ALL                 
possible Keys = null    
Key           = null
Key Len     =   null
ref         =   null        
rows        =   195 

Extra       =   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    

Extra       = 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 is the show create table with tbl1 and tbl1_test_01 join to curdef

Answer 1

Right after making a copy of the table, all the data is sitting in cache. Hence, it runs 10 times as fast.

Let's check the cache size. How much RAM? Which ENGINE? What is the value of innodb_buffer_pool_size?

Please provide SHOW CREATE TABLE for both tables.

Please qualify column names so we can tell which table each column is in.

IF BASE and purdate are in the same table, the this composite index may speed up the query (before and after copying the table):

INDEX(BASE, purdate)

In case you need more discussion, please provide EXPLAIN SELECT ....

EDIT

The copy seems to have more indexes. But it does not necessarily have a composite index containing both BASE and purdate.