Problem with huge InnoDB table

Problem with huge InnoDB table

Postby Guest » Mon 10 Jan 2005 14:43

I am using MyDAC v., Borland Builder 5 and mySQL v. 4.0.17:

I am using TMyTable component to access an InnoDB database with FetchAll = false

If I have a huge MySQL table then my application dies when setting
MyTable->Active = true;

If I run "SHOW PROCCESSLIST;" in a mySQL promt the following is shown:

Does anybody know what the problem is ?
Has the problem been fixed in later versions of MyDAC ?

Postby Paul » Wed 12 Jan 2005 15:47

Please try the last version of MyDAC from 22.12.04
Bug fixed with connection loss on long queries here
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Hanne » Fri 14 Jan 2005 15:42

I have now tried latest version of MyDAC ( but still the same problem.

Does anybody know why the "SELECT COUNT(*) ..." is run when setting MyTable->Active = true - or how to avoid it ?

Regards Hanne (author)

Postby Hanne Berthelsen » Fri 14 Jan 2005 17:39

I have found out what was wrong:

SELECT COUNT(*) was run because
MyTable->Options->QueryRecCount was true

When setting this field to false, there was no problem.

Regards Hanne
Hanne Berthelsen
Posts: 15
Joined: Thu 18 Nov 2004 07:51

Re: Problem with huge InnoDB table

Postby Ikar » Mon 17 Jan 2005 14:18

A query SELECT COUNT(*) mustn't take a lot of time and cause hanging the application. How much time does it take to execute SELECT COUNT(*) for this table, if to execute it through a separate component?
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby Hanne Berthelsen » Mon 17 Jan 2005 14:44

I have seen "SELECT COUNT(*) from " take 1 hour and 30 minutes for a table of 9,064,394 elements if run in a MySQL prompt.
But the problem only appears for InnoDB databases. For small InnoDB tables or MyISAM tables there is no problem.

I can see in the MySQL manual that one of the restrictions on InnoDB tables is that they don't keep an internal count of rows in tables so "SELECT COUNT(*)" will take some time.

I think that the MyDAC package is working properly - but it is the InnoDB tables that are not usefull if you want to know the number of elements in huge tables.

Regards Hanne
Hanne Berthelsen
Posts: 15
Joined: Thu 18 Nov 2004 07:51

Postby alec » Thu 20 Jan 2005 09:10

I'm don't like innodb, because it decrease perfomance, especially for embedded server. My self test application with embedded server and innodb=ON braked during startup and shutdown.
Now I use skip-innodb option in my.ini and it is excellent :)

Good luck!

Return to MySQL Data Access Components