# MySql Query is taking more to execute **URL:** \ **Category:** Logstash\ **Created:** [September 26, 2018, 7:05am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939 "2018-09-26T07:05:58Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 26, 2018, 7:05am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/1 "2018-09-26T07:05:59Z")
I'm migrating MYSQL data to ElasticSearch using logstash. The table has more than 9 crore records. I have tested the query in mysql workbeanch which is taking 0.032 sec to execute. When i run it from logstash, its taking more than 600 sec. What will be the reason ? Could you guys please help me ? This is my logtash conf file. input { jdbc { jdbc\_driver\_library =\> "mysql-connector-java-5.1.46.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/database" jdbc\_user =\> "abc" jdbc\_password =\> "xyz" statement\_filepath =\> "select \* from table\_name" } } I have a pagination in the query using primary key. for single fetch it will take 100000 records. page = 100000 select \* from table\_name where id between 0 and page; this page will increment by 100000 using shell script. ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [September 26, 2018, 8:41am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/2 "2018-09-26T08:41:29Z")
Does my [comment in this discussion](https://discuss.elastic.co/t/how-logstash-is-working/149143/2?u=guyboertje) help? ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [September 26, 2018, 11:18am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/3 "2018-09-26T11:18:44Z")
> [@JoseRohit](#): > > The table has more than 9 core records What does this mean? ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 26, 2018, 11:40am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/4 "2018-09-26T11:40:03Z")
Sorry 9 crore records. Actually i'm migrating XXX table from MYSQL to Elastic search using logstash. XXX table has 9 crore records set. While processing the 9 crore records using following script page = 100000 select \* from table\_name where id between 0 and page; its take 0.032 sec from MSQL Workbench. But when i access same query through logstash its taking more than 6 minutes. Could you please help me? Why its taking too much of time ? ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [September 26, 2018, 11:54am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/5 "2018-09-26T11:54:49Z")
Please use the decimal system in future. 90 million records in 10 minutes means 150 000 events (documents) per second and in 6 minutes means 250 000 events (documents) per second. Honestly, I don't think you will be able to get Logstash to run any faster. ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 26, 2018, 12:32pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/6 "2018-09-26T12:32:44Z")
Thank you. I'm not talking about the entire process of indexing, i'm talking about running times of query alone. When i run it from MYSQL workbench its talking 0.032 sec alone for 1 million records. The same query i'm running through logstash its taking more than 6.0 minutes . Fetching the result alone taking 6 minutes. That's why i'm wondering. Why its taking more time ? ---
**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:** [September 26, 2018, 12:36pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/7 "2018-09-26T12:36:03Z")
Is that 0.032 seconds the time to first results or the time to actually read out all the 1 million results? Are you verifying that you are reading out all records, e.g. by dumping them to a file? ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 27, 2018, 4:31am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/8 "2018-09-27T04:31:44Z")
Sorry, for 100 thousands records its taking 0.032 sec in a single fetch. input { jdbc { jdbc\_driver\_library = "mysql-connector-java-5.1.46.jar" jdbc\_driver\_class = "com.mysql.jdbc.Driver" jdbc\_connection\_string = "jdbc:mysql://localhost:3306/database" jdbc\_user ="abc" jdbc\_password = "xyz" statement\_filepath = **"select \* from table\_name where id between 1 and 100000 "** } } The query execution itself taking 6 minutes when i run this conf from logstash. But when i run it from MSQL Workbeanch its taking 0.032 sec Why there is a big different ? ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 27, 2018, 6:19am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/9 "2018-09-27T06:19:57Z")
input { jdbc { jdbc\_driver\_library = "mysql-connector-java-5.1.46.jar" jdbc\_driver\_class = "com.mysql.jdbc.Driver" jdbc\_connection\_string = "jdbc:mysql://localhost:3306/database" jdbc\_user ="abc" jdbc\_password = "xyz" statement\_filepath = **"select \* from table\_name where id between 1 and 100000 "** } } How the logstash will run this conf internally ? Will it write the query result to outfile ? writing the above query result to out file is taking 300 sec. But wondering why it taking 6 minutes when i run this conf from logstash. I hope you got my question . Thank you ---
**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:** [September 27, 2018, 6:53am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/10 "2018-09-27T06:53:02Z")
How large was the output when you wrote these documents to file from MSQL Workbench? ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 27, 2018, 8:53am UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/11 "2018-09-27T08:53:16Z")
The output document size is 90 MB. ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 27, 2018, 12:00pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/12 "2018-09-27T12:00:27Z")
still i'm facing the issue. please help me ---
**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:** [September 27, 2018, 12:07pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/13 "2018-09-27T12:07:17Z")
In your Logstash config, where are you sending the data? What does the rest of your configuration look like? Logstash will only read data as fast as the slowest downstream system can accept them, so that might be a bottleneck. ---
**Author:** ![JoseRohit](https://avatars.discourse-cdn.com/v4/letter/j/90ced4/32.png) [@JoseRohit](https://discuss.elastic.co/u/JoseRohit)\ **Post date:** [September 27, 2018, 12:41pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/14 "2018-09-27T12:41:23Z")
Thank you, I found the issue. Actually i missed one index in query part. when i added the index in query it run fast now. But i'm still wondering, without index in workbench it got completed with in 0.032 sec for 1 million records. Bur when i configure through logstash, the query execution time alone taking around 6 minutes. ---
**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:** [September 27, 2018, 12:58pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/15 "2018-09-27T12:58:37Z")
Well, you did not answer my questions about the rest of your configuration, so it is hard to tell. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 25, 2018, 12:58pm UTC](https://discuss.elastic.co/t/mysql-query-is-taking-more-to-execute/149939/16 "2018-10-25T12:58:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.