I really have a big problem. I use MySQL to store sentences with sentences with partial speech in a table. The table looks like this:
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| idTitle | varchar(25) | NO | PRI | NULL | |
| idReview | int(10) unsigned | NO | PRI | NULL | |
| idSentence | int(10) unsigned | NO | PRI | NULL | |
| content | text | NO | | NULL | |
| POSInfo | text | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
These are the indices in the table:
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| reviewsentences | 0 | PRIMARY | 1 | idSentence | A | 23 | NULL | NULL | | BTREE | |
| reviewsentences | 0 | PRIMARY | 2 | idTitle | A | 32087 | NULL | NULL | | BTREE | |
| reviewsentences | 0 | PRIMARY | 3 | idReview | A | 2470720 | NULL | NULL | | BTREE | |
| reviewsentences | 1 | fk_ReviewSentences_Reviews1 | 1 | idTitle | A | 983 | NULL | NULL | | BTREE | |
| reviewsentences | 1 | fk_ReviewSentences_Reviews1 | 2 | idReview | A | 494144 | NULL | NULL | | BTREE | |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I am trying to read reviews that blong will add to the review object before a specific review. I access the database through JDBC and the reading is read forever! I say 2 minutes in 26 lines! This is the Java code that I use to query the database:
public List<Review> fillupReviews(List<Review> reviews, boolean tagged){
try {
Statement stmt = dbConnection.createStatement() ;
for (Review review : reviews) {
ResultSet rs=null;
if(tagged==true){
rs = stmt.executeQuery("SELECT idSentence, POSInfo FROM reviewsentences WHERE idTitle="+review.getMovieID()+" and idReview="+review.getReviewID()+";") ;
}else{
rs = stmt.executeQuery("SELECT idSentence, content FROM reviewsentences WHERE idTitle="+review.getMovieID()+" and idReview="+review.getReviewID()+";") ;
}
while(rs.next()){
review.addTaggedSentence(rs.getInt(1),rs.getString(2));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return reviews;
}
If I access the same table with the same query through MySQL Workbench, does it take 0.296 seconds? Therefore, I assume that there must be something seriously wrong! But I really can’t understand what will go wrong, or what to change in order to speed up this damn thing. Can someone tell me?
, ! !! - !? :
public List<Review> fillupReviews(List<Review> reviews, boolean tagged){
try {
PreparedStatement selectReview=null;
if(tagged==true){
selectReview = dbConnection.prepareStatement("SELECT idSentence, POSInfo FROM reviewsentences WHERE idTitle= ? AND idReview= ?;");
}else{
selectReview = dbConnection.prepareStatement("SELECT idSentence, Content FROM reviewsentences WHERE idTitle= ? AND idReview= ?;");
}
for (Review review : reviews) {
selectReview.setString(1, review.getMovieID());
selectReview.setInt(2, review.getReviewID());
ResultSet rs = selectReview.executeQuery();
while(rs.next()){
review.addTaggedSentence(rs.getInt(1),rs.getString(2));
}
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
return reviews;
}
( , MySQL Workbench [0,3 ]). , , - , ? - ?