MySQL JDBC is very slow, don't know why

Question

I have a problem with a very slow connection between Java code and a MySQL database. I don’t know where the throat of the bottle is.

My program is more or less a chatbot. The user enters something, my program breaks the sentence into words and sends his word by word to the database. If he finds something, the user gets an output. The database is on an external server, but I also tried to connect to the computer next to me. Both are slow.

I tried the connection once in another place where I usually work, and there it was fast, most of the time.

My SQL code:

SELECT info.INFORMATION FROM INFORMATION INFORMATION, INFO_SCHLUESSEL sch
WHERE LCASE (sch.SCHLUESSELWORT) LIKE '"+ input +"%' AND info.ID_INFO = sch.ID_INFO
Order BY info.PRIORITAET DESC LIMIT 1;

(just remember if this helps to understand sql code:
schluessel = key
Schluesselwort = keyword
priorityaitet = priority)

The code for my Java database is more or less standard:

Line driver = "com.mysql.jdbc.Driver";
String dbase = "jdbc: mysql: // bla";
String dbuser = "bla"

String dbpw = "bla";

Class.forName (driver);
Connection con = DriverManager.getConnection (dbase, dbuser, dbpw);
Statement stmt = con.createStatement ();

ResultSet rs = stmt.executeQuery (query);
while (rs.next ())
{
ergebnis = rs.getString ("info.INFORMATION"); }

rs.Close ();
stmt.close ();
con.close ();

edit:

I have tried this DBMS for some time, and I can’t get it to work. It seems to be as slow as the old connection. This is an example presented on the website that I am using:

GenericObjectPool connectionPool = new GenericObjectPool (null);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory ("jdbc: mysql: // bla", "bla", "bla");
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory (connectionFactory, connectionPool, null, null, false, true);
PoolingDriver driver = new PoolingDriver ();
driver.registerPool ("example", connectionPool);
Connection conn = DriverManager.getConnection ("jdbc: apache: commons: dbcp: example");

Answer 1

I suspect this is the connection setting that is causing the problem. It would be advisable how long it takes:

Connection con = DriverManager.getConnection(dbase, dbuser, dbpw); 

and if so, check out the Apache Commons DBCP , which allows you to combine database connections.

Answer 2

Well, I think it requires a discussion of design. There are a few things you can do to improve performance. Since you are not storing anything here, it’s better to pre-load all the data in memory into some user java object, map, list, or something else, and then search in memory for the word and get the results. Another approach might be to use a batch operator, so you don’t go ahead and create or release connections for each word. Oh, and if you use batch operators, make sure the batch size matches the corresponding number, preferably a prime