Problem with huge InnoDB table

Guest · Post by **Guest** » Mon 10 Jan 2005 14:43

I am using MyDAC v. 3.30.0.12, 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:
SELECT COUNT(*) FROM mytable

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

Paul · Post by **Paul** » Wed 12 Jan 2005 15:47

Please try the last version of MyDAC 3.30.2.15 from 22.12.04
Bug fixed with connection loss on long queries here

Hanne · Post by **Hanne** » Fri 14 Jan 2005 15:42

I have now tried latest version of MyDAC (3.30.2.15) 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)

Hanne Berthelsen · Post by **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

Ikar · Post by **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?

Hanne Berthelsen · Post by **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

alec · Post by **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!

Devart Forums

Problem with huge InnoDB table

Problem with huge InnoDB table

Re: Problem with huge InnoDB table