CODEWITHSUNDEEP
javamysqlperformance
KingCrimson
KingCrimson

Reputation: 97

Search for text mysql database is really slow

I have a table on mySql with around 300,000 records. One column is a VARCHAR and it contains a link (let's say, http://www.mysite.com/123012993)

Using Java, everytime I create a new record I need to know if it already exists. The exact link must be on the database. If it's new, then proceed to insert. Else, do nothing.

So I have the following:

String selectString = "Select count(link) from records_table where link = ?";
        PreparedStatement ps = conn.prepareStatement(selectString);
ps.setString(1, "http://www.mysite.com/123012993");
ResultSet rsFinding = ps.executeQuery();

rsFinding.next();

if (t != 0) return false;
else { // do normal insert }

However, the query to search the Text is very slow, we are talking around 1 minute. The insert itself is very fast. Everything runs on localhost.

Is this the right way to search for the text? Or should I index the database?

I was thinking on implementing a hashkey and narrow the results, but a query on 300,000 records shouldn't be to heavy I believe.

Thanks

Upvotes: 0

Views: 145

Answers (1)

maaartinus
maaartinus

Reputation: 46462

A couple of things:

  • PreparedStatement should not be prepared each time again and again. Prepare and reuse.
  • Your t is defined nowhere.
  • Let the DB do the work: I guess each DB has a possibility to handle duplicates. For MySql there's INSERT ... ON DUPLICATE KEY UPDATE ...

So use this command

INSERT ? INTO records_table ON DUPLICATE KEY UPDATE link = link

The part link = link is a no-op to make the syntax looking good for the MySql parser.

There's also INSERT IGNORE which is bit easier to use (no need for the no-op), but it ignores more problems, which is bad.

I forgot to mention that you need a unique key constraint on link (a primary key is a special case of UK as thus fine too).

Upvotes: 2

Related Questions