# Discrepancy in Document Count When Importing Data from MySQL to Elasticsearch Using Logstash **URL:** \ **Category:** Elastic Search\ **Created:** [January 7, 2025, 12:10pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891 "2025-01-07T12:10:25Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Zikou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zikou/32/140193_2.png) [@Zikou](https://discuss.elastic.co/u/Zikou)\ **Post date:** [January 7, 2025, 12:10pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/1 "2025-01-07T12:10:26Z")
Hello Elasticsearch Community, I am encountering an issue with importing data from MySQL to Elasticsearch using Logstash. Here’s the context and details: * * * #### **Setup Information:** - **MySQL Table Name:** `criteria_sites` - **Elasticsearch Index Name:** `mysql-criterion-site-data` - **Pipeline Input Configuration:** I am using the JDBC plugin in Logstash to fetch data from MySQL. #### **MySQL Table Details:** - The table `criteria_sites` has **16,626 rows**. - It has a **composite primary key** consisting of: - `criterion_value_id` - `site_id` ```auto SHOW KEYS FROM criteria_sites WHERE Key_name = 'PRIMARY'; +----------------+------------+----------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------------+------------+----------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | criteria_sites | 0 | PRIMARY | 1 | criterion_value_id | A | 14030 | NULL | NULL | | BTREE | | | YES | NULL | | criteria_sites | 0 | PRIMARY | 2 | site_id | A | 16626 | NULL | NULL | | BTREE | | | YES | NULL | +----------------+------------+----------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ ``` * * * #### **Elasticsearch Output Configuration in Logstash:** ```auto output { elasticsearch { hosts => ["http://ip-address:9200"] index => "mysql-criterion-site-data" document_id => "criterion-site-%{criterion_value_id}" # Uses only `criterion_value_id` as the ID } } ``` * * * #### **Issue:** When I import the table using Logstash, the document count in Elasticsearch doesn’t match the number of rows in the MySQL table. - **MySQL Table Rows:** 16,626 - **Elasticsearch Index Statistics:** ```auto yellow open mysql-criterion-site-data FSkeQzvVQKa4PwIDbnYV0Q 1 1 14030 2596 1.3mb ``` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 7, 2025, 12:15pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/2 "2025-01-07T12:15:44Z")
You are creating the key based on only one component of the primary key, so it is natural and expected that the number of documents in Elasticsearch match the cardinality of this, which as you pointed out is 14030. If you want all documents inserted you need to create the document id based on both components of the primary key. ---
**Author:** ![Zikou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zikou/32/140193_2.png) [@Zikou](https://discuss.elastic.co/u/Zikou)\ **Post date:** [January 7, 2025, 12:16pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/3 "2025-01-07T12:16:40Z")
how to solve the issue and find all the 16,626 rows in elasticsearch? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 7, 2025, 12:18pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/4 "2025-01-07T12:18:14Z")
> [@Zikou](#): > > ```auto > output { > elasticsearch { > hosts => ["http://ip-address:9200"] > index => "mysql-criterion-site-data" > document_id => "criterion-site-%{criterion_value_id}" # Uses only `criterion_value_id` as the ID > } > } > > ``` Change this to something like this: ```auto output { elasticsearch { hosts => ["http://ip-address:9200"] index => "mysql-criterion-site-data" document_id => "%{site_id}-%{criterion_value_id}" } } ``` Naturally you will need to delete everything from the index before rerunning as you otherwise will get duplicates. ---
**Author:** ![Zikou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zikou/32/140193_2.png) [@Zikou](https://discuss.elastic.co/u/Zikou)\ **Post date:** [January 7, 2025, 12:19pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/5 "2025-01-07T12:19:33Z")
I will try and give you update ---
**Author:** ![Zikou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zikou/32/140193_2.png) [@Zikou](https://discuss.elastic.co/u/Zikou)\ **Post date:** [January 7, 2025, 12:28pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/6 "2025-01-07T12:28:20Z")
thanks man it worked yellow open mysql-criterion-site-data l8otXpdqQOOeRnK8cAfGwg 1 1 16626 49878 2.2mb 2.2mb can you explain to me why at first the number is mismatched, im new to elasticsearch and logstash ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 7, 2025, 12:31pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/7 "2025-01-07T12:31:52Z")
You were indexing documents with a document id (primary key in Elasticsearch) that only contained one component of the key you have in your data base. There are per your statistics only 14030 unique values in this key component in your database so there can never be more unique document ids than that in Elasticsearch. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 4, 2025, 12:32pm UTC](https://discuss.elastic.co/t/discrepancy-in-document-count-when-importing-data-from-mysql-to-elasticsearch-using-logstash/372891/8 "2025-02-04T12:32:02Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.