Anyone care to help optimize a MySQL query?

Question

Here's the query:

SELECT COUNT(*) AS c, MAX(`followers_count`) AS max_fc, 
       MIN(`followers_count`) AS min_fc, MAX(`following_count`) AS max_fgc,
       MIN(`following_count`) AS min_fgc, SUM(`followers_count`) AS fc,
       SUM(`following_count`) AS fgc, MAX(`updates_count`) AS max_uc,
       MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
WHERE `twitter_id` IN (SELECT `followed_by` 
                       FROM `relations` 
                       WHERE `twitter_id` = 123);

The two tables are profiles and relations. Both have over 1,000,000 rows, InnoDB engine. Both have indexes on twitter_id, relations has an extra index on (twitter_id, followed_by). The query is taking over 6 seconds to execute, this really frustrates me. I know that I can JOIN this somehow, but my MySQL knowledge is not so cool, that's why I'm asking for your help.

Thanks in advance everyone =)

Cheers, K ~

Updated

Okay I managed to get down to 2,5 seconds. I used INNER JOIN and added the three index pairs. Here's the EXPLAIN results:

id, select_type, table, type, possible_keys, 
    key, key_len, ref, rows, Extra

1, 'SIMPLE', 'r', 'ref', 'relation', 
    'relation', '4', 'const', 252310, 'Using index'

1, 'SIMPLE', 'p', 'ref', 'PRIMARY,twiter_id,id_fc,id_fgc,id_uc', 
    'id_uc', '4', 'follerme.r.followed_by', 1, ''

Hope this helps.

Another update

Here are the SHOW CREATE TABLE statements for both tables:

CREATE TABLE `profiles` (
  `twitter_id` int(10) unsigned NOT NULL,
  `screen_name` varchar(45) NOT NULL default '',
  `followers_count` int(10) unsigned default NULL,
  `following_count` int(10) unsigned default NULL,
  `updates_count` int(10) unsigned default NULL,
  `location` varchar(45) default NULL,
  `bio` varchar(160) default NULL,
  `url` varchar(255) default NULL,
  `image` varchar(255) default NULL,
  `registered` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  `relations_timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`twitter_id`,`screen_name`),
  KEY `twiter_id` (`twitter_id`),
  KEY `screen_name` USING BTREE (`screen_name`,`twitter_id`),
  KEY `id_fc` (`twitter_id`,`followers_count`),
  KEY `id_fgc` (`twitter_id`,`following_count`),
  KEY `id_uc` (`twitter_id`,`updates_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `relations` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `twitter_id` int(10) unsigned NOT NULL default '0',
  `followed_by` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`id`,`twitter_id`),
  UNIQUE KEY `relation` (`twitter_id`,`followed_by`)
) ENGINE=InnoDB AUTO_INCREMENT=1209557 DEFAULT CHARSET=utf8

Wow, what a mess =) Sorry!

Answer 1

A join would look something like this:

SELECT COUNT(*) AS c,
MAX(p.`followers_count`) AS max_fc,
MIN(p.`followers_count`) AS min_fc,
MAX(p.`following_count`) AS max_fgc,
MIN(p.`following_count`) AS min_fgc,
SUM(p.`followers_count`) AS fc,
SUM(p.`following_count`) AS fgc,
MAX(p.`updates_count`) AS max_uc,
MIN(p.`updates_count`) AS min_uc,
SUM(p.`updates_count`) AS uc
FROM `profiles` AS p
INNER JOIN `relations` AS r ON p.`twitter_id` = r.`followed_by`
WHERE r.`twitter_id` = 123;

To help optimize it you should run EXPLAIN SELECT ... on both queries.

Answer 2

SELECT COUNT(*) AS c,
  MAX(`followers_count`) AS max_fc, MIN(`followers_count`) AS min_fc,
  MAX(`following_count`) AS max_fgc, MIN(`following_count`) AS min_fgc,
  SUM(`followers_count`) AS fc, SUM(`following_count`) AS fgc,
  MAX(`updates_count`) AS max_uc, MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
JOIN `relations`
  ON (profiles.twitter_id = relations.followed_by)
WHERE relations.twitted_id = 123;

might be a bit faster, but you'll need to measure and check if that is indeed so.

Answer 3

Create the following composite indexes:

profiles (twitter_id, followers_count)
profiles (twitter_id, following_count)
profiles (twitter_id, updates_count)

and post the query plan, for God's sake.

By the way, how many rows does this COUNT(*) return?

Update:

Your table rows are quite long. Create a composite index on all the fields you select:

profiles (twitter_id, followers_count, following_count, updates_count)

so that the JOIN query can retrieve all the values it need from that index.

Answer 4

count(*) is a very expensive operation under the InnoDB Engine, have you tried this query without that piece? If it's causing the most processing time then maybe you could keep a running value instead of querying for it each time.

Answer 5

I'd approach this problem from a programmers angle; I'd have a seperate table (or storage area somewhere) that stored the max,min and sum values associated with each field in your original query and update those values everytime I updated and added a table record. (although deleting may be problematic if not handled correctly).

After the original query to populate these values is complete (which is the almost the same as the query you posted), you're essentially reducing your final query to getting one row from a data table, rather than computing everything all at once.