MYSQL Slow query, how to optimize?

Question

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 ;

how many rows is this query returning? are you sure you have to use left outer join? — Jordi Flores
– Jordi Flores, Commented Jul 26, 2016 at 14:18
so when i call it for a list of users and LIMIT 1. how do you call your list??? show us your backend code outside mysql? — Alex
– Alex, Commented Jul 26, 2016 at 14:20
I added the structure. This query returns 1 row. I have on the servlet a list of user ids getted from the request and i need to retrieve those datas for every single user. — Andrea Biagioni
– Andrea Biagioni, Commented Jul 26, 2016 at 14:28

Rick James · Accepted Answer · 2016-07-27 15:54:30Z

3

("Read this entire answer before starting to take action.")

This does not make sense:

PRIMARY KEY (`id`,`fb_id`),
UNIQUE KEY `fb_id` (`fb_id`),
UNIQUE KEY `id` (`id`)

Tentatively, change to

PRIMARY KEY(fb_id),
INDEX(id)

on the assumption that you usually look up the record by fb_id.

If you say "list of users". Do you mean that WHERE fb_id IN ( ... )? Probably not since you have LIMIT 1. Or do you mean that you call that SELECT repeatedly?

Gag! likes_face has an id that you are not using, plus fb_id that you are using but is not indexed. Use the same id for both tables: either get rid of AUTO_INCREMENT and change code logic for likes_face, or get rid of id and make fb_id the PK for both tables.

Are these tables 1:1?

Something is wrong. Why does likes_face haveAUTO_INCREMENT=1182636, while there seem to be only 173 users? If the tables are 1:1, are you usingREPLACE`? You will run out of ids!

Restarting...

If the tables are 1:1:

totally get rid of id on both tables and have PRIMARY KEY(fb_id) on both tables,
make fb_id CHARACTER SET ascii if Facebook limits ids to ascii,
no secondary indexes needed on either table.

If likes_face is a count of likes for each day:

Get rid of id on both tables,
make fb_id CHARACTER SET ascii if Facebook limits ids to ascii,
Give likes_face PRIMARY KEY(fb_id, date).
no secondary indexes needed on either table.
Use IODKU to do the increment/insert into the likes-per-day table.

If the tables are 1:many in some other way, I can't make sense of the schema.

edited Jul 27, 2016 at 15:54

answered Jul 26, 2016 at 16:09

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrea Biagioni Over a year ago

I mean i call SELECT repeatedly, and it's the second case 1:many, Thanks!

Bill Karwin · Accepted Answer · 2016-07-26 16:02:35Z

2

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 (or maybe before denormalizing), you should think about caching the data.

edited Jul 26, 2016 at 16:02

answered Jul 26, 2016 at 14:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

5 Comments

Andrea Biagioni Over a year ago

Thanks Bill, this reduced the time to 0.01 instead of 0.2! There's no thing that i can edit in the query (or maybe the structure) to improve the performance further?

Rick James Over a year ago

If nlikes is being updated frequently, it is not wise to have it in any index.

Bill Karwin Over a year ago

@RickJames, with respect, I think that's an overgeneralization. It's true there is more overhead to updating an index versus not updating an index. But it can still be worth including a column in an index, depending on the queries you need to run. The difference is what you consider higher priority -- faster writes or faster reads. The covering index effect can give a huge improvement to some queries, enough to justify the overhead.

Rick James Over a year ago

Hi Bill. You have valid points. For this use case, I am assuming that "likes" are frequently updated and less frequently "selected". (Maybe that assumption is wrong.) In really busy "like" systems, the bumping of likes becomes the bottleneck. Andrea probably does not yet have a "really busy system", but I wanted to avoid that. (As I point out in my answer, the are other things that need fixing before a 'covering' index bubbles to be the "most important".)

Andrea Biagioni Over a year ago

nlikes is never update, just stored every tot time in a new record automatic way, my priority is to have the faster reads as possible.

Archit · Accepted Answer · 2016-07-26 16:58:07Z

0

You can use EXPLAIN of statement of MYSQL. Optimizing Queries with EXPLAIN

Someone suggested of creating indexes. Creating index is good option but avoid indexes if that column going to be modified frequently.

edited Jul 26, 2016 at 16:58

answered Jul 26, 2016 at 15:24

Archit

1491 gold badge2 silver badges8 bronze badges

2 Comments

Rick James Over a year ago

Re the second link... Be cautious about apply Oracle advice to MySQL.

Archit Over a year ago

Thanks @RickJames. I have removed that link. However that was intended for the indexes not specifically to mysql or Oracle.

Collectives™ on Stack Overflow

MYSQL Slow query, how to optimize?

QUERY:

CODE

CREATE TABLE CODE

USERS

likes

3 Answers 3

1 Comment

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

QUERY:

CODE

CREATE TABLE CODE

USERS

likes

3 Answers 3

1 Comment

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related