If you are using the MySQL connector to sync more than one MySQL table/view into Algolia with a Replace or Save task, you may notice that some fields from your table(s) are not being indexed.
Assuming there is a common objectID for each table, the last table “wins” and effectively overwrites the object with only the columns from that last table.
There are three general approaches that would resolve this issue, in order of recommendation:
1. Sync a single “search-ready” table.
Create a table/view in MySQL that joins everything you want in one record, and sync only that table in the Algolia connector. (ie: product_search_view = join product + product_images + product_stock_availability). If you control the database, a "clean" SQL table is almost always the best long-term solution.
2. Use Partial Update tasks for subsequent tables.
To avoid overwriting fields from the previously indexed table use a single Replace task only for the "main" table (ie: product), and then create a new Partial Update task for each of the additional tables to update/enrich the existing records. There are a few important considerations for this solution:
- Ordering matters (the base record must exist in Algolia first) the connector does not guarantee task execution order. You must ensure the Replace task for the "main" table runs first (via scheduling or manual sequencing)
-
Attributes removed from the source in one of the "supplemental" tables (ie:
product_images) won’t be removed from Algolia unless you manage that removal explicitly.
3. Sync each table into a separate Algolia index via a separate Replace task.
Though this approach might require the least effort in terms of the indexing process itself, the downsides include:
- Multiply the number of records indexed to Algolia by the number of MySQL tables.
- The requirement that you implement complex custom logic to essentially perform a "join" on these separate records in order to present results to the end user.