MySQL prcedures take too much time and the tables are very large

Question

I have a large live database where around 1000 users are updating 2 or more updates every minute. at the same time there are 4 users are getting reports and adding new items. the main 2 tables contains around 2 Million and 4 Million rows till present.

Queries using these tables are taking too much time, even simple queries like:

"SELECT COUNT(*) FROM MyItemsTable"  and  "SELECT COUNT(*) FROM MyTransactionsTable"

are taking 10 seconds and 26 seconds

large reports now are taking 15mins !!! toooooo much time.

All the table that I'm using are innodb

is there any way to solve this problem before I read about reputation ??

Thank you in advance for any help

Edit Here is the structure and indexes of MyItemsTable:

CREATE TABLE `pos_MyItemsTable` (
  `itemid` bigint(15) NOT NULL,
  `uploadid` bigint(15) NOT NULL,
  `itemtypeid` bigint(15) NOT NULL,
  `statusid` int(1) NOT NULL,
  `uniqueid` varchar(10) DEFAULT NULL,
  `referencenb` varchar(30) DEFAULT NULL,
  `serialnb` varchar(25) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `user` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `pass` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `expirydate` date DEFAULT NULL,
  `userid` bigint(15) DEFAULT NULL,
  `insertdate` datetime DEFAULT NULL,
  `updateuser` bigint(15) DEFAULT NULL,
  `updatedate` datetime DEFAULT NULL,
  `counternb` int(1) DEFAULT '0',
  PRIMARY KEY (`itemid`),
  UNIQUE KEY `referencenb_unique` (`referencenb`),
  KEY `MyItemsTable_r04` (`itemtypeid`),
  KEY `MyItemsTable_r05` (`uploadid`),
  KEY `FK_MyItemsTable` (`statusid`),
  KEY `ind_MyItemsTable_serialnb` (`serialnb`),
  KEY `uniqueid_key` (`uniqueid`),
  KEY `ind_MyItemsTable_insertdate` (`insertdate`),
  KEY `ind_MyItemsTable_counternb` (`counternb`),
  CONSTRAINT `FK_MyItemsTable` FOREIGN KEY (`statusid`) REFERENCES `MyItemsTable_statuses` (`statusid`),
  CONSTRAINT `MyItemsTable_r04` FOREIGN KEY (`itemtypeid`) REFERENCES `itemstypes` (`itemtypeid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `MyItemsTable_r05` FOREIGN KEY (`uploadid`) REFERENCES `uploads` (`uploadid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

yes sure all tables contains indexes, before that the database became large the everything was good — , Apr 10 '14 at 07:03
woooow !! the first one took 0.5 secs and the second one 1.6secs — , Apr 10 '14 at 07:06
when i replaced it again by (*) it didn't take too much time maybe because this query is now in the cache queries — , Apr 10 '14 at 07:08
Can you add the structure of your main tables and the current indexes ? — d.raev, Apr 10 '14 at 07:10
@user3518239 If you switch out `SELECT` with `SELECT SQL_NO_CACHE` you can test the performance while avoiding the cache. — h2ooooooo, Apr 10 '14 at 07:48
well I used it but the first time the query took 22.3secs, second time 13sec, third time 1.3secs :/ I think the cache is still working :( — , Apr 10 '14 at 08:00
when i was reading in this site [http://dev.mysql.com/doc/refman/5.0/en/select.html] I read this sentence: SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This option can be used only for top-level SELECT statements, not for subqueries or following UNION. \n Is it better to use SQL_BUFFER_RESULT ?? — , Apr 10 '14 at 11:35

score 1 · Accepted Answer · edited May 23 '17 at 12:21

Just having few indexes does not mean your tables and queries are optimized.

Try to identify the querties that run the slowest and add specific indexes there.

Selecting * from a huge table .. where you have columns that contain text / images / files will be aways slow. Try to limit the selection of such fat columns when you don't need them.

future readings:

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

and some more advanced configurations:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

source

UPDATE:
try to use composite keys for some of the heaviest queries, by placing the main fields that are compared in ONE index:

`MyItemsTable_r88` (`itemtypeid`,`statusid`, `serialnb`), ...

this will give you faster results for queries that complare only columns from the index :

SELECT * FROM my_table WHERE `itemtypeid` = 5 AND `statusid` = 0  AND `serialnb` > 500

and extreamlly fast if you search and select values from the index:

SELECT `serialnb` FROM my_table WHERE `statusid` = 0  `itemtypeid` IN(1,2,3);

This are really basic examples you will have to read a bit more and analyze the data for the best results.

Thank you for pointing me , well i have to read a lot before I consider any answer as correct answer. thank you and ill inform you when ill find a solutions :) — , Apr 10 '14 at 07:34
for now ill consider your answer as correct .. i'll ask you later if i have a new comment. thanks — , Apr 10 '14 at 09:36
Thank you for the Update I will add more indexes on many columns that I use in Where clause with "AND". thanks :) — , Apr 10 '14 at 12:03

MySQL prcedures take too much time and the tables are very large

1 Answers1