SELECT ip.query, b.name, b.cover, ip.co AS VALUE
FROM (
    SELECT query, COUNT(ip_times.query) AS co
    FROM ip_times
    WHERE DATE_SUB(CURDATE(), INTERVAL 100 DAY) <= DATE(ip_times.date)
        AND path = '/GetBookById'
    GROUP BY ip_times.query
    ORDER BY co DESC
    LIMIT 23
) ip 
LEFT JOIN books b ON ip.query = b.id

code must be written with high readability. First, find out the top 23 books ID, and then go to the join table to query the most efficient

SELECT
    id
    name,
    cover
FROM books
WHERE EXISTS(
    SELECT 1 FROM (
        SELECT
            query,
            COUNT(1) as value
        FROM ip_times
        WHERE
            path='/GetBookById'
            AND DATE(a.date) > DATE_SUB(CURDATE(), INTERVAL 100 DAY)
        GROUP BY query
        ORDER BY value desc
        LIMIT 23
    ) a WHERE a.query=books.id
)

from the DATE_SUB (CURDATE (), INTERVAL 100DAY) < = DATE (ip_times.date) in the sql statement, we can see that the student wants to query the time before ip_times.date > = 100days. Then I think we must know the time DATE_SUB (CURDATE (), INTERVAL 100DAY) . It can be calculated in the code first, not in the sql statement. In addition, DATE (ip_times.date) this value is the date part of ip_times.date , which I don't think is necessary, such as 2019-01-09 14:00:00 and 2019-01-09 are both > = 100 days ago, so compare directly with ip_times.date, do not use date () function.

so this sql can be optimized to ip_times.date > = '2018-10-01, to see if the query is much faster.

in a word, it is possible not to use functions in sql query statements, which seriously affects the efficiency of sql queries.

set indexes on fields that are often needed as query criteria?

ip_times.date is varchar? If it is varchar, it is recommended to change it to date. Try not to use functions in where, that is, use them, and don't use them on index columns.
in fact, this tuning is difficult to give a good answer to the table structure that does not know what your business is going to do.
you can use explain to query the execution plan. And then targeted tuning.

from the sql statement DATE_SUB (CURDATE (), INTERVAL 100DAY) < = DATE (ip_times.date), we can see that the student wants to query the time before ip_times.date > = 100days. Then I think we must know the time DATE_SUB (CURDATE (), INTERVAL 100DAY). We can calculate it in the code first, not in the sql statement. In addition, DATE (ip_times.date) this value is to take the date part of ip_times.date, I do not think it is necessary, such as 2019-01-09 14:00:00 and 2019-01-09 are > = that time 100 days ago, so directly use ip_times.date to compare, do not use date () function.

explain + a little bit of mysql metaphysics = can solve all the slow SQL card problems, and at worst, you can know which part of the problem has gone wrong and then improve it, and sometimes it is no longer a problem that mysql itself can solve. The above answers may be reasonable, but I think there are two ways to ask questions about the performance of sql: 1. Give the original table with all the data (this query is actually not complex, it takes 5 seconds, and the number of data in the table may not be hundreds of thousands); 2. Give a mysql execution plan (just as debug is essential).