How to speed up MySQL database / query?

Question

I have two tables in my MySQL database, users and tweets, as follows:

TABLE users (
  uid int(7) NOT NULL AUTO_INCREMENT,
  twitter_uid int(10) NOT NULL,
  screen_name varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  tweets int(6) NOT NULL,
  followers_count int(7) NOT NULL,
  statuses_count int(7) NOT NULL,
  created_at int(10) NOT NULL,
  PRIMARY KEY (uid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

TABLE tweets (
  tweet_id int(11) NOT NULL AUTO_INCREMENT,
  `query` varchar(5) NOT NULL,
  id_str varchar(18) NOT NULL,
  created_at int(10) NOT NULL,
  from_user_id int(11) NOT NULL,
  from_user varchar(256) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (tweet_id),
  KEY id_str (id_str)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

The tweets table contains over 2 million records. I have put the unique users (taken from tweets.from_user) in the users table. It now contains 94,100 users. I now want to count the number of tweets each user made, as follows (in PHP):

res = db_query('SELECT uid, screen_name FROM users WHERE tweets = 0 LIMIT 150');
while ($user = db_fetch_object($result)) {
  $res2 = db_query(
    "SELECT COUNT(tweet_id) FROM tweets WHERE from_user = '%s'",
    $user->screen_name
  );
  $cnt = db_result($result2);
  db_query("UPDATE users SET tweets = %d WHERE uid = %d", $cnt, $user->uid);
}

This code however, is EXTREMELY slow. It takes about 5 minutes to count the tweets of 150 users. Going at this rate, it will take about 3 days to complete this task for all users.

My question is - I MUST be missing something here. Perhaps there is a more efficient query possible or I should change something to the database structure? Any help would be greatly appreciated :)

check out Quassnoi's site, explainextended.com, look at his examples, and maybe ask him — david
– david, Commented Nov 20, 2011 at 16:59

maraspin · Accepted Answer · 2011-11-20 17:07:50Z

6

I think worst problem here is having multiple queries. That's most likely worse than just an issue with indexes. You should try to have one query only.

UPDATE users 
SET users.tweets = (SELECT COUNT(tweet_id) 
                    FROM tweets 
                    WHERE tweets.from_user = users.uid 
                    AND users.tweets =0
                   )

edited Nov 20, 2011 at 17:07

answered Nov 20, 2011 at 16:54

maraspin

2,42321 silver badges16 bronze badges

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

4 Comments

a1ex07 Over a year ago

+1 I totally agree with you. In the worst case there are 151 SELECT queries and 150 UPDATEs. All of them can be replaced with 1 UPDATE.

maraspin Over a year ago

cool. :-) Just one thing I'm unsure is the limit he has... is that really needed on the select, @Reveller?

Pr0no Over a year ago

The limit is just there because of the slowness of the whole thing. I downloaded WGET for windows and created a Scheduled Task which runs the above code every 5 minutes (in 150 users increments), to prevent Apache from exceeding the 300 seconds timeout limit :)

Pr0no Over a year ago

Thanks! With an index on from_user and the above query, the whole thing took only 58 seconds LOL. Engineers FTW! (I'm a business school student myself :P)

Inna · Accepted Answer · 2011-11-20 16:52:33Z

2

have you indexed all relevant attributes? escpecially from_user should have an index!

answered Nov 20, 2011 at 16:52

Inna

3673 silver badges12 bronze badges

Comments

ruakh · Accepted Answer · 2011-11-20 16:59:46Z

2

I'd start by condensing all of that into a single UPDATE statement:

UPDATE users
   SET tweets =
        ( SELECT COUNT(1)
            FROM tweets
           WHERE tweets.from_user = users.screen_name
        )
 WHERE users.tweets = 0
 LIMIT 150
;

and then I'd look at indices. In particular, make sure there's an index on tweets.from_user. (See http://dev.mysql.com/doc/refman/5.0/en/create-index.html for how to create an index on a table columns.)

answered Nov 20, 2011 at 16:59

ruakh

185k29 gold badges292 silver badges324 bronze badges

Comments

Branko Dimitrijevic · Accepted Answer · 2011-11-20 17:49:23Z

While you could significantly speed-up the updating of users.tweets by "condensing" these SQL statements into one (as suggested by other answers), what will you do when user makes a new tweet? How will know that users.tweets needs to be updated again?

One way would be to make a trigger that updates users.tweets whenever a row is deleted from or inserted into the tweets table, or when tweets.from_user is modified.
You could also remove the users.tweets altogether and just count the tweets dynamically on as-needed basis.

In any case, to speed up the SELECT COUNT(tweet_id) FROM tweets WHERE from_user = '%s' query, you'll need to create an index on {from_user}. Since tweet_id is NOT NULL, COUNT(tweet_id) is equivalent to COUNT(*) - otherwise a composite index on {from_user, tweet_id} would be needed.

prusswan · Accepted Answer · 2011-11-20 16:52:44Z

1

A first step would be adding indexes to the columns that are frequently used as search criteria.

answered Nov 20, 2011 at 16:52

prusswan

7,1514 gold badges44 silver badges65 bronze badges

4 Comments

maraspin Over a year ago

can't wholly agree. first step is most likely about avoiding so many queries. Indexes are also important, of course. bust most likely not the worst issue here. In doubt, benchmarking and explain can be your friends, of course. :-)

prusswan Over a year ago

Given 90k entries with no indexes, that's a big no-no for me from practical experience. Optimizing the query itself is the not-so-low hanging fruit :P

maraspin Over a year ago

I see your point. Considering the numbers that actually makes lot of sense. But, from experience, indexes are never a replacement for bad design choices. ;-)

prusswan Over a year ago

Yeah, I think a 5 minute query on a million entries (I just noticed) isn't that bad. It could have been a lot worse

Collectives™ on Stack Overflow

How to speed up MySQL database / query?

5 Answers 5

4 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related