I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this:
- Id: (int 11, primary key)
- Codigo: (longtext)
- Act_mas: (double)
- Act_menos: (double)
- Ind_mas: (double)
- Ind_menos: (double)
- Cap_mas: (double)
- Cap_menos: (double)
- Mensaje: (longtext)
- Fecha: (datetime)
- SuministrosId (int 11, this is a foreign key of a table with 62 rows, 21 columns that has another 2 foreign key, one with 44 rows and 11 columns and the other with 2 rows and 2 columns)
I guess I could change the datatype of
Mensaje to something like
VARCHAR(10), since normally the value has less than 10 characters and probably
LONGTEXT is an overkill.
I could also change column datatype, from
INT, since all the values should be smaller than 1.000.000 (at the moment the biggest value is 795.600, and it's very strange to have such a value).
The database and the table is using 'InnoDB' engine.
Actually a query like
SELECT * FROM table WHERE SuministrosId = 4 and Fecha >= "2015-01-01" takes about 3-5 seconds to load. Maybe its not too much but I think it could be lot less.
I tried to change the datatype of some columns but it was terribly slow and I had to stop it, should I give another try? Also I added an index to
SuministrosId but it seemed to be the same.
If other data is needed feel free to ask. Thank you in advance! (And sorry for my English :c)
CPU: Intel(R) Xeon(R) CPU E5-2609 v4 @ 1.70 GHz
RAM: 32 GB
OS: Windows Server 2012 R2 Standard
MySQL Version: 5.7.11
The database is used by an external java program that typically uses queries like the described above:
SELECT * FROM consumos WHERE SuministrosId = 4 and Fecha >= "2015-01-01", this is the most used query since it's the "core" for the job done here. Some other queries are used to list clients like:
SELECT * FROM clientes, or
SELECT * FROM suministros. But those tables are really small and they are not a problem.
SHOW CREATE TABLE for the big one:
CREATE TABLE `consumos` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Codigo` longtext NOT NULL, `Act_mas` double NOT NULL, `Act_menos` double NOT NULL, `Ind_mas` double NOT NULL, `Ind_menos` double NOT NULL, `Cap_mas` double NOT NULL, `Cap_menos` double NOT NULL, `Mensaje` longtext NOT NULL, `Fecha` datetime NOT NULL, `SuministrosId` int(11) NOT NULL, PRIMARY KEY (`Id`), KEY `IX_FK_Contadorsuministrosset` (`SuministrosId`), KEY `Fecha` (`Fecha`), CONSTRAINT `FK_Contadorsuministrosset` FOREIGN KEY (`SuministrosId`) REFERENCES `suministros` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=13002432 DEFAULT CHARSET=latin1