Database located in MySql
Slave table via ODBC connected to Access
Two tables are connected by the field "idsub" 1: M.
Characteristics of the main table:
- number of lines - 1.0 million (maybe more)
- number of columns ~ 20 pieces
Characteristics of the subordinate table:
- line count -> 1.0 million
- number of columns ~ 20 pieces
If you increase the number of rows in a subordinate table - more than 100,000, then when you move the cursor through the records of the main table, the computer starts to slow down.

Question.
What can be done to avoid braking? Is the use of such tools acceptable - MySql, Access for such a database?
What software is desirable to use in such situations?
Or how to organize the interaction between the tables?

File is located by reference

enter image description here

  • one
    Are you sure that the computer slows down, and not Access? It would not be bad to indicate the power of the PC on which braking occurs. - iluxa1810
  • one
    What is the role of Access? You use it as a wrapper for reports, and you pull everything out of MySql? - iluxa1810

2 answers 2

I would not play Access and MySql between myself.

Access is primarily a program for working with desktop Access databases, and as I understand it, you are using it as a tool for administering MySql

If the question is only in administering MySql, then I would choose one of these .

Does MySql use for this amount of data?

Depends on the needs.

If you are going to use the DBMS only for data storage and you do not need any cool chips of powerful DBMS, then MySql will be enough for you. But a lot of things will have to be implemented in the client application, since MySql’s opportunities are scarce and, for example, T-SQL is not there.

Why does it slow down?

There may be several options:

  1. Weak iron For the database you need to have a powerful PC.
  2. Bad db architecture.
  3. There are no indices on the fields that participate in the links.
  4. Access ... All the same, this program is designed primarily to work with Access databases, not MySql ...
  • one
    @koverflow, I'm not sure, but Access caches data when executing queries. For example, there are 2 Access databases, where there are tables weighing 1.5 GB each. And so, if 1 access in another and to make UNION, then Access DB will break. If I were you, I would look for an Alternative to Access. All the same, it is primarily for working with Access DB. - iluxa1810
  • one
    @koverflow, and generally, it seems, ODBC is a universal driver => may not work optimally. If you work with MySql through the correct utility, then the native driver should be used => performance boost. - iluxa1810
  • one
    @ koverflow, I gave the link in the answer habrahabr.ru/post/142385 . - iluxa1810
  • one
    @koverflow, writing your own solution makes sense if you have not found a suitable utility that meets your needs, otherwise this is a bicycle invention. - iluxa1810
  • one
    @koverflow, well, for example, such a thing is dbschema.com/forms-and-reports.html . Do you need a tool, as I understand it, that will allow you to create new records without knowing the queries? - iluxa1810

With such volumes, linking the local and server tables is very disadvantageous in terms of performance. Move all the tables to the server and use pass-trough queries, this will maximize the use of the capacity of the server database, and Aktsess as a frontend is very good.

  • Did I understand you correctly? Placed everything on the server Made a form on which you need to enter the query criteria. Made a query "To server" SELECT tbl5. *, Tbl5.idsub FROM tbl5 WHERE ((((tbl5.idsub) = [Forms]! [03_frm_00_tbl_00]! [03_frm_idsub])); As a result, it gives an error. How can I fix the error? prntscr.com/e59ias - koverflow
  • Instead of [Form]! [03_frm_00_tbl_00]! [03_frm_idsub] you need to substitute a specific value, i.e. test request is changed before each update of the form - Sergey S.