Indexes

Tables created from remote database connections and remote data files are not standard database tables. These tables depend entirely on their remote connection or data file. While most support local indexes, tables created from JSON and XML files are an exception.

📌 Do not create indexes on JSON or XML tables, as this will make them inaccessible.

When querying a table through a remote database connection, the remote server uses its own indexes. Therefore, creating local indexes for these tables is usually unnecessary.

However, there are two exceptions to this rule:

• A local primary key is required to enable transactions in WP Data Access.
• A local index can improve performance for columns frequently used in joins.

Joining Tables from Different Data Sources

A key feature is the ability to join tables from different data sources (for example, joining an MS Access table with an Oracle table and a CSV spreadsheet). Without local indexes on the join columns, the database management system (DBMS) must load the entire tables to perform the join. In these scenarios, creating local indexes can dramatically improve performance.

📌 Do not join large tables without local indexes! Such a query might take a very long time to complete.

Indexing CSV Files

Tables created from CSV files can also be indexed. The plugin maintains these indexes automatically. You can even add a primary key to enable transaction support in WP Data Access.

📌 Any changes made to these tables are not written back to the original remote data file, even if its public URL remains valid. Synchronization is one-way only.