1

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 Codigo and 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 Act_mas to Cap_menos, to 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)

EDIT

Computer info:

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

Query explain: enter image description here

Profile: enter image description here

  • Not really possible to answer unless you provide full index definitions, along with any exact performance issues you are currently hitting, along with explain plans for them – Philᵀᴹ Jul 15 '16 at 7:46
  • 1
    SHOW CREATE TABLE My_Table\G for all tables. Text of typical queries. Explain Plan for same. CPU, RAM, Storage (RAID config &c.), OS, would all be good for starters. – Vérace Jul 15 '16 at 8:06
  • Is it really needed for all tables? I have 28 tables in this database, the biggest one is 11M rows, the second one 2,8M rows and the third 149k rows. Any way to get that info in just one query or do I need to run 28 querys? I'll add typical queries and server info on the first post. Finally, what do you mean with "Explain Plan"? I'm not really an expert of MySQL and databases, sorry :S and thank you for your help – Slei Jul 15 '16 at 8:17
  • @Vérace added some more info, tell me if you need something else. – Slei Jul 15 '16 at 8:35
  • Oh yeah - version of MySQL? Can you profile the consumos query? HDD config? Put a KEY on Fecha and see does that help - I think it will. You appear to have two KEYs on Id - if it's a PK it'll have one anyway. Same for SuministrosId if it's an FK, it's OK like that. Remove the extra ones! With 32GB of RAM, what happens if you run two queries (same) twice in a row? – Vérace Jul 15 '16 at 8:56
1

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.)

Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.

  • Wow, this index helped a lot. Queries are taking now 0.003 seconds to load. About the innodb_buffer_pool_size I had to set it at 3G, if I put more the server never starts don't know why. – Slei Jul 18 '16 at 6:14
  • Fixed, I was using 32 bits instead of 64. – Slei Jul 18 '16 at 7:05
  • With 32-bit MySQL, even 2G may be dangerously high. – Rick James Jul 18 '16 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.