How can an app use data from mySQL with large file size

I want to run the app with mySQL database, which has a very large file size (about 3 million rows) causing problems when accessing the app. How can I fix it? So that the app does not read or check this file. Before this table is retrieved

Create ”View” with reduced amount of rows and read that view to create your table. Then you edit your app. Then on that table you created with your app, apply security filter to rule out the rows which are not necessarily used within your apps to reduce the amount of the data being returned to your apps.
Then you swith the data source from VIEW to proper table with the full set of the rows.

1 Like

Are you really using MyISAM storage engine? :scream: :scream: :scream:

The error message appears to be clear about the problem:

image

A column in your AppSheet table does not exist in your MySQL table.

In the app editor, in Data >> Columns, for the dbm_detail table, use Regenerate Structure:

Based on the error, I’d guess you dropped or renamed a database table column without making AppSheet aware of the change.

2 Likes

After fixing the error as @Steve has pointed out, you could use Views in the database as @tsuji_koichi has described.


But there is one glaring issue here, it’s a HUGE table. There will still be functions that are slowed by a table of this size (e.g. writing new rows into the table - especially if there is additional indexing applied).

Typically tables are of this size because they are also storing rarely used, if ever, historical records.

If this is your case and it is feasible, I would HIGHLY recommend having two tables for this data - one for the frequently used, day-to-day records and another for ALL historical/inactive records. Pull in only the day-to-day table into your main app. Then create a separate app dedicated ONLY to viewing the history, if still needed.

You will also need an archival process to move inactive records from your main app table into the historical table. This can be implemented as part of the history app.

Your main day-to-day app will then remain performant and snappy many years to come!!!

1 Like

See also: