4

I'm facing a performances problem in our MariaDB server.

We have a table linking users to pages, a user belongs to 1+ pages.

I have a page that have users and I want to get the 10 best pages based on users they share with the current page (page_id).

Here is the schema :

CREATE TABLE IF NOT EXISTS `pages_users3` (
  `user_id` bigint(20) unsigned NOT NULL,
  `page_id` bigint(20) unsigned NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `likes` int(11) NOT NULL DEFAULT '0',
  `shares` int(11) NOT NULL DEFAULT '0',
  `posts` int(11) NOT NULL DEFAULT '0',
  `interactions` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`page_id`,`user_id`) `clustering`=yes,
  KEY `user_page` (`user_id`)
) ENGINE=TokuDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `compression`='tokudb_zlib' AUTO_INCREMENT=30127543590 ;

I have a query that can retrieve similar pages of a page based on users intersections :

SELECT `pages_users`.`page_id` , COUNT(*) AS count
FROM `pages_users`
LEFT JOIN `pages_users` AS `pu2` ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE `pages_users`.`page_id` != *PAGE_ID*
AND `pu2`.`page_id` = *PAGE_ID* /* To avoid getting the current page in results */
GROUP BY `pages_users`.`page_id`
ORDER BY `count` DESC
LIMIT 10

Unfortunaly this query runs in about 1m30s, so I would like to optimize it...

Here is the result of an explain :

+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
| id   | select_type | table       | type   | possible_keys                                                                     | key                                            | key_len | ref                                    | rows      | Extra                                                     |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
|    1 | SIMPLE      | pages_users | index  | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_interactions_index | 20      | NULL                                   | 165354919 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | pu2         | eq_ref | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_unique             | 16      | soprism_prod.pages_users.user_id,const |         1 | Using index                                               |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

I'm requesting your help because I spent many hours trying to optimize that query and I feel confused :( I'm open to anything, from refactoring to changing server parameters... Thank you !

Some additional insights :

SELECT COUNT(user_id) FROM `pages_users` WHERE `page_id` = *PAGE_ID*;
+----------------+
| COUNT(user_id) |
+----------------+
|         146132 |
+----------------+
1 row in set (0.08 sec)

SELECT COUNT(user_id) FROM `pages_users`;
+----------------+
| COUNT(user_id) |
+----------------+
|      165354918 |
+----------------+
1 row in set (1 min 0.98 sec)

Hardware : Quad Core Xeon, 32Gb RAM, Raid 1 2x2Tb

  • I would try to create an index like CREATE INDEX ... ON pages_users (user_id, page_id). If that works and you keep that index, you can get rid of the index for user_id because it will be redundant. – Lennart Jan 18 '19 at 7:55
0

Please try: (edited 2014-12-18: replaced Left with INNER join):

SELECT 
    `pages_users`.`page_id` , 
    COUNT(`pu2`.`page_id`) AS `count`
FROM 
    `pages_users`
    INNER JOIN `pages_users` AS `pu2` 
        ON `pu2`.`user_id` = `pages_users`.`user_id`
           AND `pu2`.`page_id` = *PAGE_ID* 
WHERE 
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY 
    `pages_users`.`page_id`
ORDER BY 
    `count` DESC
LIMIT 10

or as an alternative

SELECT 
    `pages_users`.`page_id` , 
    COUNT(`pu2`.`pu2_page_id`) AS `count`
FROM 
    `pages_users`
    INNER JOIN 
    (
        SELECT  
            `pu2_intern`.`page_id` AS `pu2_page_id`
        FROM
            `pages_users` AS `pu2_intern`
        WHERE
            `pu2_intern`.`page_id` = *PAGE_ID*
    )  AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE 
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY 
    `pages_users`.`page_id`
ORDER BY 
    `count` DESC
LIMIT 10

You could also add another index just on the field page_id:

KEY `page` (`page_id`)

I hope it could help you.

| improve this answer | |
  • I tried your queries, here is the results : 10 rows in set (14 min 5.62 sec) for the second one. The first one was even longer... Adding the key did no significant difference :( – user3793124 Dec 18 '14 at 7:57
  • I'm sorry then for a bad idea :( - if you might though please try replacing left joins with inner joins (because in fact your query behaved as inner joined by the fact of using clause AND pu2.page_id = *PAGE_ID* on a left joined table). I hope this might bring some effect. I've edited the query to reflect the change – T_G Dec 18 '14 at 10:00
  • Could you please also explain more detailed what this query should give as a result? – T_G Dec 18 '14 at 12:08
  • new results, not better :( : 15 min 3.97 sec and 11 min 4.25 sec) – user3793124 Dec 18 '14 at 13:38
  • Yes, I have a page that have users and I want to get the 10 best pages based on users they share with the current page (page_id). – user3793124 Dec 18 '14 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.