servoy application very slow with large innodb table

Classic Servoy
1

Hi there,

I am running servoy with mysql 4.0.14 database server on Windows 2000 Machine. The tables are stored in InnoDB format. I have a table called media which store files, movies, images etc for various purpose. I had these media files stored in the file system as folder/files in my previous filemaker system. I have written a servoy method to migrate the data into the media table. Some how the script ran a long long time and completed successfully at the end. Now I have around 1.25GB approx. of data in the media file.

Now the issue has come. The servoy application become extremely slow. But the same database is being accessed by the php website. But it seems it is running normally. If I drop the database and recreate it and its tables and import data into all the database except the media table, then the servoy application has become normal. There is a file called ibdata1 under the folder c:\mysql\data\ which is really huge in size about 1.5GB.

Any idea why?

Thanks
Hameed

2

Hello,

I have also upgraded to the mysql version 4.1.7 . First I have exported the data from the 4.0 server using SELECT INTO OUTFILE command. Then imported in the 4.1 server using LOAD DATA INFILE command. This was quite fast as it took around 20mins for 1.5GB of data in an old server.

But the issues are still worse. Servoy is still slow. Any idea please? I need to urgently GO LIVE on this solution. But this issue is holding me up.

PLEASE HELP!!

Thanks

3

Are you accessing the media table data in a related way from within servoy? if so create an index on the foreign key in the media table.
Servoy uses always the (indexed) pk for form based row access, only in the case of retrieving related data an foreign key index could desirable.

4

Yes I am using non-indexed foriegn key. I could create one and test. But the number of records in the media table is just around 2500 currently. So I believe this is some other issue. However I am going give your idea a try.

Thanks

5

is servoy also slow on forms that don’t access that media table?

what does that media table looks like. How do you access that one, only through a relation? What happens if you build a small solution directly and only on that media table?

6

First of all I just tried to create an index in the media table which is 1.25 GB in size, taking ever to complete the index creation operation. Any idea why? Should I cancel it now??

is servoy also slow on forms that don’t access that media table?

The solution is NOT slow when navigating through a form which does not have relation to the media table.

I have just created a form (detail view) for media table. I just navigated through it. It is NOT slowing down solution.

Looking forward to solve this problem ASAP. Please help!

7

please try first with create foreign index on mysql

8

Thanks Jan,

The un-indexed foreign key was causing the speed problem. It was not actually the size of the media database.

Thanks a lot for you suggestions. Really helped!!

9

The primary key in any table is by default indexed or not?
Do I have to manually create indexes for each of them so that servoy handles loading data faster?

Also if I create a relation do I need to create an index for both primary and foreign key to make servoy run faster?

I am using mysql 4.1.

Advise please..
Thanks

10

Yes, a pk is indexed by default

11

Thanks Jan,

If I create a relation do I need to create an index for both primary and foreign key to make servoy run faster?

12

Only create indexes when there are performance problems, somethimes (many) indexes do more harm than good.
So yes if you have performance issues you migth consider indexing the relation keys. (as I origionally suggested “try indexing your foreign key”, the relation LHS is in many cases the pk and need no index)

13

Ok Thanks,

I get your advice.

But I am creating indexes for a few fields which I mostly search on.

For example in a customer table I would search first_name, last_name email, address etc

Doing this cause some harm for the speed?

Thanks for your advice.

14

Also I used to search fields using LIKE operator. Somthing like

first_name = "%vo%;

to find the word servoy. In this case index is any useful?

15

that is db depended.
Some db do have full text index. But most of the time a % at front of youre value can’t be indexed.

so serv% can most of the time be indexed.
but %voy not. Or the db supports a full text index.

16

Thanks ! understood!!