2

I saw a question on stackoverflow mysql self join performance. The question mentioning that self join will increase query performance. For example this query

select m2.* from message m1,message m2
where m1.id=m2.id and m1.thumbs_up <=98
and (m1.id<13 or m1.thumbs_up<98)
order by m1.thumbs_up desc,m1.id desc

explain result

+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys         | key           | key_len | ref        | rows  | Extra                    |
+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+
|  1 | SIMPLE      | m1    | range  | PRIMARY,thumbs_up_key | thumbs_up_key | 4       | NULL       | 21574 | Using where; Using index |
|  1 | SIMPLE      | m2    | eq_ref | PRIMARY               | PRIMARY       | 4       | test.m1.id |     1 | NULL                     |
+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+

have better performance than

select *
from message
where thumbs_up <=98 and (id<13 or thumbs_up<98)
order by thumbs_up desc,id desc

explain result

+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+
| id | select_type | table   | type | possible_keys         | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | message | ALL  | PRIMARY,thumbs_up_key | NULL | NULL    | NULL | 43148 | Using where; Using filesort |
+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+

I tried the query using profiling and yes the performance is actually better specially in create sort index. Is it possible to explain why as I found it interesting thing to know (I tried using explain query but didn't find good explanation).

update:

create table query

CREATE TABLE `message`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `user_id` int(11) NOT NULL,
    `content` text NOT NULL,
    `create_time` int(11) NOT NULL,
    `thumbs_up` int(11) NOT NULL DEFAULT '0', /*Vote Count*/
    PRIMARY KEY (`id`),
    KEY `thumbs_up_key` (`thumbs_up`,`id`)
)

Also I removed all indexes except the primary key index, the performance difference was huge the like double the speed.

5
  • We need the query plan to know for sure. It would also be optimal to provide the create table statement and any indexes on the table already. Off the top of my head, I'd guess it's able to utilize two different indexes and increase speed with parallelism. So it may be doing more work but it is faster. Mar 29, 2018 at 14:52
  • I updated the question if it possible to see the result Mar 29, 2018 at 15:41
  • What's the point of doing a self join on the primary key? (from message m1,message m2 where m1.id=m2.id) Seems completely useless to me. Mar 29, 2018 at 16:58
  • Please provide your test results. "better performance" is not accurate enough. Mar 29, 2018 at 18:19
  • I am not sure why when I did the test now both queries gave me same result but at morning the first query execution time was 1 sec and the second one 2 second in fact now the second one give better result like 0.2 sec difference. Not sure why here slideshare.net/suratbhati/… they mention that the first query is better. Mar 29, 2018 at 22:25

2 Answers 2

2

The likely reason for the difference you are seeing: The 2nd must haul * around in a tmp table and sort it, whereas the 1st avoids such.

You have a fully ordered, but 2-part, index. The Optimizer will do a good job if you are going forward from where you left off, but this is a different situation -- You want every row before some particular row (98:13).

Give this a try; it may trick the Optimizer into using the index for both the WHERE and the ORDER BY:

select *
    from message
    where thumbs_up <=98
    HAVING (id<13 or thumbs_up<98)
    order by thumbs_up desc, id desc

I think it will say the 'good' Using index but not the 'bad' Using filesort.

Another technique for comparing two formulations:

FLUSH STATUS;
SELECT SQL_NO_CACHE ...;
SHOW SESSION STATUS LIKE 'Handler%';
2

I really cannot see the point of using self-join in this query. It is 100% equivalent to this:

select m.* 
from message m
where m.thumbs_up = 98 and m.id < 13 
   or m.thumbs_up < 98
order by m.thumbs_up desc, m.id desc 
-- limit N 
  ;

I would expect an index on (thumbs_up, id) to be used by this.

4
  • Thats my question why the selfed join query is much faster than your query? Mar 29, 2018 at 17:25
  • 1
    Please provide execution plans for this query then. Your question has 2 queries, which are both different than this one. Also times that the various queries needed on average and the table size. Mar 29, 2018 at 18:05
  • your query and the second query are the same in term of execution plan. Mar 29, 2018 at 22:26
  • @MohdAlomar Please take the time to run ypercube's proposed query and post for us EXPLAIN .... for documentation visibility, since you have the data. If ROWS are the same in the ypercube explain as in your second query, It will be slower than your first query for the reason explained by Rick James, hauling around * data through the tmp table. May 13, 2018 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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