select te.topic_id,te.entry_id
from topic_entry te
WHERE te.topic_id in (
SELECT topic_id
from topic_user
where user_id ='xyz')
AND te.level=4
NO! Don't use "IN ( SELECT ... )"; it does not optimize well (until 5.6.5).
select te.topic_id, te.entry_id
from topic_entry te
JOIN topic_user tu ON tu.topic_id = te.topic_id
where tu.user_id ='xyz'
AND te.level=4
Also helpful:
On topic_users:
KEY `user_id` (`user_id`), -->
KEY `user_id` (`user_id`, topic_id),
How many levels are there? How many rows have level=4? If the cardinality is poor, then this won't be used:
KEY `level` (`level`),
After making those changes, I expect "EXPLAIN SELECT ..." to show that it is picking topic_user as the first table, and say only a few "Rows" are needed.
Lesser issues:
Do you really need BIGINT? INT UNSIGNED lets you get up to 4 billion, and saves 4 bytes per row. (See also MEDIUMINT, etc)
> `entry_object` text,
If you can live with VARCHAR(255), it will perform slightly better.