Optimize SQL query (Facebook-like application)
- by fabriciols
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?
