MySQL Forums
Forum List  »  MyISAM

Re: query scanning all rows
Posted by: Rick James
Date: September 15, 2012 12:44PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2489
September 12, 2012 01:20PM
Re: query scanning all rows
1465
September 15, 2012 12:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.