Andrea Biagioni Andrea Biagioni - 4 months ago 18
SQL Question

MYSQL Slow query, how to optimize?

I have a big problem, I'm not very good with SQL... I have a database in mysql and when I do this query I have a response time around the 0.2s, so when i call it for a list of users (in a servlet) the response time goes around lots of seconds..

QUERY:



SELECT visible,nlikes,nomecognome,profile_img,users_face.id
FROM users_face
LEFT OUTER JOIN `likes_face`
on (users_face.fb_id = likes_face.fb_id)
WHERE users_face.fb_id =? and users_face.token_valid=1
ORDER BY date DESC limit 1


Is there any way to optimize this code or any good resource to study the optimization of queries?




CODE



ArrayList<SocialMan> mans = new ArrayList<>();
PreparedStatement ps;
int nlikes, userid;
String nomeCogn, prof;
boolean visible;
FacebookClient facebookClient = new DefaultFacebookClient(token, Version.VERSION_2_6);
com.restfb.Connection<User> myFriends;
myFriends = facebookClient.fetchConnection("me/friends",User.class, Parameter.with("limit", 999));
for (User u : myFriends.getData()) {
nlikes = -1;
userid = -1;
nomeCogn = "ERROR";
prof = "ERROR";
visible = false;
try {

ps = con.prepareStatement("SELECT visible,nlikes,nomecognome,profile_img,users_face.id FROM users_face LEFT OUTER JOIN `likes_face` on (users_face.fb_id = likes_face.fb_id) WHERE users_face.fb_id =? and users_face.token_valid=1 ORDER BY date DESC limit 1");

ps.setString(1, "" + u.getId());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
nlikes = rs.getInt("nlikes");
userid = rs.getInt("id");
nomeCogn = rs.getString("nomecognome");
prof = rs.getString("profile_img");
visible = rs.getBoolean("visible");
}
} catch (SQLException ex) {
Logger.getLogger(FaceLikes.class.getName()).log(Level.SEVERE, null, ex);
}
// System.out.println("NOMECOGNOME: "+nomeCogn);
if (userid != -1 && visible) {
mans.add(new SocialMan(nomeCogn, userid, prof, nlikes));
}
}
nlikes = -1;
userid = -1;
nomeCogn = "ERROR";
prof = "ERROR";


CREATE TABLE CODE



USERS



CREATE TABLE IF NOT EXISTS `users_face` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fb_id` varchar(45) NOT NULL,
`fb_token` varchar(300) NOT NULL,
`nomecognome` varchar(100) NOT NULL,
`data_iscrizione` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`profile_img` varchar(255) NOT NULL,
`visible` int(11) NOT NULL DEFAULT '1',
`TOKEN` varchar(255) NOT NULL,
`locale` varchar(255) NOT NULL,
`token_valid` tinyint(1) NOT NULL,
PRIMARY KEY (`id`,`fb_id`),
UNIQUE KEY `fb_id` (`fb_id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=173 ;


likes



CREATE TABLE IF NOT EXISTS `likes_face` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`nlikes` int(11) NOT NULL,
`fb_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1182636 ;

Answer

This is all I can come up with:

ALTER TABLE users_face ADD INDEX (fb_id, token_valid);

ALTER TABLE likes_face ADD INDEX (fb_id, nlikes, date);

You can't optimize the sorting, because the date column is in your non-primary table.


If you want to optimize further, the next thing to do is to move the nlikes and date columns into the users_face table, then make the index on that table over the following columns in this order: (fb_id, token_valid, date, nlikes).

Then you can skip the join in your query, and the sort order will be the order of the index, so that will be optimized.

After that, you should think about caching the data.

Comments