Problem with huge InnoDB table

Problem with huge InnoDB table

Postby 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 ?
Guest
 

Postby 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
Paul
 
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 (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
 

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?
Ikar
 
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!
alec
 


Return to MySQL Data Access Components