3

I have a simple query and it takes only 0.2 seconds when I execute it on mysql console. But when I execute this query via jdbc from my runnable java jar, it takes about 15 seconds to get the data (20000 rows).

This query is so simple and the field in my WHERE clause is indexed and I am selecting two columns only.

I tried both BoneCP connection pool and simple jdbc connection. But in both cases the result is almost the same. It looks there is something taking too much time while getting data from mysql to java side but I can not imagine how. So I can not come up with a solution.

Query example:

SELECT eventType, eventId  FROM users WHERE uid=55;

uid is indexed. It takes 0.2 second from mysql console but 15 seconds via my jar. By the way 15 second is really the time that takes while executeQuery() is being executed.

note: Before I am posting that question I searched in here, but could not find a real answer.

Solved. See my comment below

bAris
  • 111
  • 3
  • 9
  • you are executing your query directly on mysql server or remotely from any gui tool when it is taking 0.2 seconds. – Zafar Malik Sep 22 '15 at 10:05
  • And you did use a `PreparedStatement` and `executeQuery()`? And close all? PreparedStatement has for the first time a bit of overhead, but is then faster and better. Try autocommit=false. Try giving the application a bit more memory. – Joop Eggen Sep 22 '15 at 10:05
  • directly from the same host. via terminal. @ZafarMalik – bAris Sep 22 '15 at 10:07
  • now check remotely from your local system by any query browser like sqlyog, mysqladmin, monyog etc...and check the performance. – Zafar Malik Sep 22 '15 at 10:18
  • it is the same again, nothing different @ZafarMalik – bAris Sep 22 '15 at 10:43
  • @bAris: I just want to check if it is javacode issue or your local network...as you are getting same time remotely also it means issue related with your javacode.. – Zafar Malik Sep 22 '15 at 10:49
  • of course it is because of the javacode but the thing is I have no clue about it. It is a simple query and executeQuery() call. @ZafarMalik – bAris Sep 22 '15 at 11:09
  • Could you show us the java code? Maybe you do something wrong in it, so obvious that you don't see it. – StephaneM Sep 22 '15 at 11:25
  • **Issue solved:** All queries that I tried on mysql console was previously executed by my java application and that query was already cached by MySQL. So that is why console gives fast response. I tried some uids that are not processed by my application yet and yes!, that one is also slow on mysql console. So as a result, response to queries are always slow both from console and via java. It was just an illusion. – bAris Sep 22 '15 at 14:19
  • 15 exec time sounds like bad DbDesign – Antoniossss Sep 22 '15 at 14:25
  • Actually we have more than 1 million row and multiple occurences for the same uid in that table @Antoniossss – bAris Sep 22 '15 at 15:03
  • @bAris then again i smell bad design in here. Sorry – Antoniossss Sep 22 '15 at 20:19
  • If uid is a VARCHAR, then quote '55'. – Rick James Oct 11 '15 at 03:57

0 Answers0