title: MySql database slow response with multiple large tables
body: <p>I am new using Mysql and I am having problems when trying to read large tables of the data base. I have created the next data base:</p>
  
  <pre><code>CREATE DATABASE `chamber_master_db` /*!40100 DEFAULT CHARACTER SET utf8 */
  </code></pre>
  
  <p>with 80 tables as the one below:</p>
  
  <pre><code>CREATE TABLE `chamber_el_801_server_tb` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `Chamber_id` varchar(20) NOT NULL DEFAULT 'NA',
    `Date` date NOT NULL,
    `Time` varchar(20) NOT NULL DEFAULT 'NA',
    `Status` varchar(20) NOT NULL DEFAULT 'NA',
    `EWR` varchar(20) NOT NULL DEFAULT 'NA',
    `Program` varchar(30) NOT NULL DEFAULT 'NA',
    `TestLeg` varchar(20) NOT NULL DEFAULT 'NA',
    `Test` varchar(20) NOT NULL DEFAULT 'NA',
    `Temperature` double NOT NULL DEFAULT '0',
    `Humidity` double NOT NULL DEFAULT '0',
    `Channel3` double NOT NULL DEFAULT '0',
    `Setpoint1` double NOT NULL DEFAULT '0',
    `Setpoint2` double NOT NULL DEFAULT '0',
    `Setpoint3` double NOT NULL DEFAULT '0',
    `ChamberStatus` int(11) NOT NULL DEFAULT '0',
    `TestEquipment` varchar(20) NOT NULL DEFAULT 'NA',
    `LoadRack` varchar(20) NOT NULL DEFAULT 'NA',
    PRIMARY KEY (`id`),
    KEY `index2` (`Date`)
  ) ENGINE=InnoDB AUTO_INCREMENT=44754 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>I have two indexes, one is for the 'id' which is the sequential entry number of the records and the other is for the date, since I will be querying the table by date (I put these indexes because I read that using indexes could speed up the query process).</p>
  
  <p>The 80 tables of the data base are getting a new record every minute, so after a few weeks the tables are very large.</p>
  
  <p>When I query a set of records for a period of time of one table the database response is fast. The problem is when I query a set of records for a period of time of ALL tables in the data base (this query is for statistics purposes of my web application), even if a do the query from the mysql workbench 5.2.46 where the data base is running.</p>
  
  <p>I have also read that the engine used by the data base could affect the data base but I am not sure which could be the best for this application.</p>
  
  <p>The query I am using is the next:</p>
  
  <pre><code>commandLine = "SELECT count(*) FROM " + table + " WHERE Status = '" + "Run" + "' AND Date BETWEEN '" + startdate + "' AND '" + enddate + "'";
  _mysqlcommand.CommandText = commandLine; 
  Run= Convert.ToDouble(_mysqlcommand.ExecuteScalar()) / totalrows * 100;
  </code></pre>
  
  <p>This query is done 5 times for each table in order to know the number of rows for different Status and then this is repeated inside a cycle for the 80 tables in the data base, and this is what it takes up to one minute so far.</p>
  
  <p>Then the question is what do you recommend I can do to have a quick response from the data base when querying all 80 tables in the same routine.</p>