Tables created from remote database connections and remote data files are not normal database tables. These tables are depending on their remote connection or data file. Most of these tables support local indexes, except tables created from JSON and XML files. Do not create indexes on these tables. The table will no longer be accessible if you do.
When a table is queried through a remote database connection, the remote database server uses available indexes when applicable. There is usually no need to create local indexes for these tables. There are two exceptions to this rule:
- A local primary key is needed to enable transactions in WP Data Access.
- A local index might increase performance for columns used in joins.
You can join tables from different data sources. You can for example join an MS Access table, an Oracle table, and a CSV spreadsheet. Without local indexes on columns used to join these tables, the DBMS needs to load these tables entirely to perform a join. In these cases, local indexes can increase performance dramatically. Do not join large tables without local indexes! Such a query might take forever…
Tables created from a CSV file can be indexed as well. The plugin maintains these indexes automatically. It is even possible to add a primary key to these tables, which enables transaction support in WP Data Access. Table changes however are not written to the original remote data file, even if the public URL to this file remains valid. Synchronization works in one direction only.
Example CSV file with primary key and inline editing enabled #
The following screenshot demonstrates a table created from a CSV file. After loading the CSV file into the database, I added a primary key on column Empno and enabled inline editing for all columns (except the primary key which is generally not editable in WP Data Access).
Important #
Don’t create indexes on tables created from a JSON or XML file. The index makes your table inaccessible (removing the index will make the table accessible again).
