Optimize SQL query (Facebook-like application)

Posted by fabriciols on Stack Overflow See other posts from Stack Overflow or by fabriciols
Published on 2012-07-02T02:27:38Z Indexed on 2012/07/02 3:16 UTC
Read the original article Hit count: 157

Filed under:
|
|

My application is similar to Facebook, and I'm trying to optimize the query that get user records. The user records are that he as src ou dst. The src is in usermuralentry directly, the dst list are in usermuralentry_user.

So, a entry can have one src and many dst.

I have those tables:

mysql> desc usermuralentry ;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_src_id     | int(11)          | NO   | MUL | NULL    |                |
| private         | tinyint(1)       | NO   |     | NULL    |                |
| content         | longtext         | NO   |     | NULL    |                |
| date            | datetime         | NO   |     | NULL    |                |
| last_update     | datetime         | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
10 rows in set (0.10 sec)


mysql> desc usermuralentry_user ;
+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| id                | int(11) | NO   | PRI | NULL    | auto_increment |
| usermuralentry_id | int(11) | NO   | MUL | NULL    |                |
| userinfo_id       | int(11) | NO   | MUL | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

And the following query to retrieve information from two users.

mysql> explain

SELECT *
FROM usermuralentry AS a
   , usermuralentry_user AS b
WHERE a.user_src_id IN ( 1, 2 )
   OR
   (
      a.id = b.usermuralentry_id
         AND b.userinfo_id IN ( 1, 2 )
   );
+----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table | type | possible_keys                                                                             | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | usermuralentry_id,usermuralentry_user_bcd7114e,usermuralentry_user_6b192ca7 | NULL | NULL    | NULL |  147188 |                                                |
|  1 | SIMPLE      | a     | ALL  | PRIMARY                                                                                   | NULL | NULL    | NULL | 1371289 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+
2 rows in set (0.00 sec)

but it is taking A LOT of time...

Some tips to optimize? Can the table schema be better in my application?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about query