MySQL Forums
Forum List  »  Performance

Re: Index suggestion needed for performance improvement
Posted by: Rick James
Date: October 04, 2015 01:15PM

> key: e_c_d_v -- Note which index it used
> key_len: 3 -- enabled is 1 byte + category is 2; so it is using both `enabled` and `category` but not the other fields.
> rows: 11508 -- The _estimated_ number of rows to read.
> Rows_examined: 1525431 -- The _actual_ number of rows with that `enabled` and `category`.

How much RAM do you have? What is the value of innodb_buffer_pool_size? That setting should typically be 70% of RAM.

1525431 rows is a lot. Since you managed to read all of them in 2.2 seconds, I suspect they were all cached in the buffer_pool (and the buffer_pool is big enough).

There is _no_ index that will prevent reading 1525431 rows, building a tmp table, sorting it, and finally delivering what LIMIT says.

> this issue fixed after i created index enabled_seeders_upload_date

Did the EXPLAIN change? I suspect is is very similar. If it is different, I would be excited to see it.

> where can i learn how to create indexes ?

http://mysql.rjweb.org/doc.php/index_cookbook_mysql
(It does not cover everything, but I think it is a big starting point.)
It effectively says INDEX(enabled, category) is the best. You have at least 2 indexes that _start_ with those fields, so they are virtually as good. (Hence, I did not push for adding this one.)

Here is a technique that _might_ speed it up _some_:

    SELECT  content.title,content.og_name,content.record_num,content.category,
            content.upload_date,content.hash,content.comments_count,
            content.verified,content.uploader,content.size,content.seeders,
            content.leechers
        FROM  content
        JOIN 
          ( SELECT  record_num
                FROM  content
                WHERE  enabled = 1
                  AND  category IN (800,801,802,803,804,805,806,807,808,809,
                                    810,811,812,813,814,815,816,817,818,819,820)
                ORDER BY  seeders DESC , upload_date DESC
                LIMIT  0,25
          ) x ON content.record_num = x.record_num; 

# together with this "covering" index for the subquery:
INDEX(enabled, category, seeders, upload_date, record_num)

Why it _might_ help:
1. The subquery will rummage through 1525431 _narrow_ rows in the _covering_ index.
2. It will create a narrow tmp table for sorting and extracting 25 record_nums.
3. The outer SELECT will look up only the 25 records.

Caveat: You probably need ORDER BY seeders DESC , upload_date DESC at the end to get the rows in the right order. But this is a very minor filesort (25 rows, versus 1525431).

The principles behind this 'trick' are:
1. "If FROM ( SELECT... ) will significantly decrease the number of rows looked at, the overall query may run faster."
2. "A covering index is faster."

The caveat comes in that the added complexity costs something.

Another technique that _might_ help. Or it might make things worse:
INDEX(enabled, seeders, upload_date)
This would be beneficial only if those categories happened to be near the end of the seeders.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index suggestion needed for performance improvement
824
October 04, 2015 01:15PM


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.