MySql database slow response with multiple large tables

Question

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:

CREATE DATABASE `chamber_master_db` /*!40100 DEFAULT CHARACTER SET utf8 */

with 80 tables as the one below:

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

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).

The 80 tables of the data base are getting a new record every minute, so after a few weeks the tables are very large.

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.

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.

The query I am using is the next:

commandLine = "SELECT count(*) FROM " + table + " WHERE Status = '" + "Run" + "' AND Date BETWEEN '" + startdate + "' AND '" + enddate + "'";
_mysqlcommand.CommandText = commandLine; 
Run= Convert.ToDouble(_mysqlcommand.ExecuteScalar()) / totalrows * 100;

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.

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.

You might want to add an index to the field `Status`, then measure again. — Bjoern, Jun 24 '13 at 17:48

score 1 · Answer 1 · answered Jun 24 '13 at 17:48

For the query you are using you should have a composite index on Status + Date. Part of your issue is that since Status is not part of your index, it is having to do a full table scan to filter out on status. Give that a shot and see if it performs better.

MySql database slow response with multiple large tables

1 Answers1