# What will be the best way to create ES index for mulit-join tables from Mysql database **URL:** \ **Category:** Logstash\ **Created:** [January 30, 2018, 2:49am UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558 "2018-01-30T02:49:39Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![viphuangwei](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/viphuangwei/32/59306_2.png) [@viphuangwei](https://discuss.elastic.co/u/viphuangwei)\ **Post date:** [January 30, 2018, 2:49am UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/1 "2018-01-30T02:49:39Z")
We need 11 tables to join together to generate the search result in MySQL database. We use Elasticsearch as our full-text search engine. There is no join operation in ES. solution 1. write join query in logstash to create the super table index. 2. create individual index for each table then do the join at application side.(i do not want this way) 3. to use other software? when i try to use solution 1, the issues are 1) what should be the tracking-column, what should be the where cause in the query so that the index can be incrementally increased. Is there any good suggestion? ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [January 30, 2018, 6:39am UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/2 "2018-01-30T06:39:29Z")
> so that the index can be incrementally increased. What do you mean by this? ---
**Author:** ![viphuangwei](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/viphuangwei/32/59306_2.png) [@viphuangwei](https://discuss.elastic.co/u/viphuangwei)\ **Post date:** [January 30, 2018, 2:35pm UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/3 "2018-01-30T14:35:28Z")
I just want the index can be incrementally updated when the Mysql table has been updated. I am not sure how to write the super join query in the logstash. what should be the primary key for that super-join-table, what should be the tracking-column, should i added timestamp for those individual table? how to achieve incremental update. ---
**Author:** ![viphuangwei](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/viphuangwei/32/59306_2.png) [@viphuangwei](https://discuss.elastic.co/u/viphuangwei)\ **Post date:** [January 30, 2018, 2:43pm UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/4 "2018-01-30T14:43:08Z")
I know that it's straight forward to achieve incremental update the index for a single Mysql table. There's some difficult for multi-table-join-query. 1. what should be the tracking-column for that super-join-table, 2. There are 11 sub tables, when and how to update the super-join-table ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 27, 2018, 2:43pm UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/5 "2018-02-27T14:43:21Z")
Closed on February 27, 2018, 2:43pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 27, 2018, 2:43pm UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/6 "2018-02-27T14:43:22Z")
Opened on February 27, 2018, 2:43pm UTC ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 4, 2022, 5:12am UTC](https://discuss.elastic.co/t/what-will-be-the-best-way-to-create-es-index-for-mulit-join-tables-from-mysql-database/117558/7 "2022-11-04T05:12:13Z")
Closed on November 4, 2022, 5:12am UTC