title: How to improve the performance of table scans with innodb
body: <p>Brief: Is there any way to improve the performance of table scans on InnoDB tables?</p>
  
  <p>Please, do not suggest adding indexes to <em>avoid</em> table scans. (see below)</p>
  
  <p>innodb_buffer_pool_size sits at 75% of server memory (48 GB/64GB)
  I'm using the latest version of Percona (5.7.19) if that changes anything</p>
  
  <p>Longer: We have 600Gb of recent time series data (we aggregate and delete older data) spread over 50-60 tables.  So most of it is "active" data that is regularly queried.  These tables are somewhat large (400+ numeric columns) and many queries run against a number of those columns (alarming) which is why it is impractical to add indexes (as we would have to add a few dozen).  The largest tables are partitioned per day.</p>
  
  <p>I am fully aware that this is an application/table design problem and not a "server tuning" problem.  We are currently working to significantly change the way these tables are designed and queried, but have to maintain the existing system until this happens so I'm looking for a way to improve things a bit to buy us a little time.</p>
  
  <p>We recently split this system and have moved a part of it to a new server.  It previously used MyISAM, and we tried moving to TokuDB which seemed appropriate but ran into some weird problems.  We switched to InnoDB but performance is really bad.  I get the impression that MyISAM is better with table scans which is why, barring any better option, we'll go back to it until the new system is in place.</p>
  
  <p>Update</p>
  
  <p>All tables have pretty much the same structure:
  -timestamp
  -primary key (varchar(20) field)
  -about 15 fields of various types representing other secondary attributes that can be filtered upon (along with an appropriately indexed criteria first)
  -And then about a few hundred measures (floats), between 200-400.</p>
  
  <p>I already trimmed the row length as much as I could without changing the structure itself.  The primary key used to be a varchar(100), all measures used to be doubles, many of the secondary attributes had their data types changed.</p>
  
  <p>Upgrading hardware is not really an option.</p>
  
  <p>Creating small tables with just the set of columns I need would help some processes perform faster.  But at the cost of creating that table with a table scan first and duplicating data.  Maybe if I created it as a memory table.  By my estimate, it would take a couple of GB away from the buffer pool.  Also there are aggregation processes that read about as much data from the main tables on a regular basis, and they need all columns.</p>
  
  <p>There is unfortunately a lot of duplication of effort in those queries which I plan to address in the next version.  The alarming and aggregation processes basically reprocess the entire day's worth of data every time some rows inserted (every half hour) instead of just dealing with new/changed data.</p>
  
  <p>Like I said, the bigger tables are partitioned, so it's usually a scan over a daily partition rather than the entire table, which is a small consolation.</p>
  
  <p>Implementing a system to hold this in memory outside of the DB could work, but that would entail a lot of changes on the legacy system and development work.  Might as well spend that time on the better design.</p>
  
  <p>The fact that InnoDB table are so much bigger for the same data as MyISAM (2-3x as big in my case) really hinders the performance.</p>