I'm making a chat like Kakao Talk with MySQL, but the query is extremely slow depending on the number of LIMITs

Hello. Like Kakao Talk, we are implementing a chat that shows the past conversation when you look up while looking at the recent conversation.

SELECT * FROM chat WHERE user_id = user ID order by chat_id descrit 10
SELECT * FROM chat WHERE user_id = user ID order by chat_id limit 100

We pulled it out with the same query as above, and then re-aligned it from the server in a low state to give it to the front side.

The problem is that the speed of the above two queries is very different.

The user_id is indexed, chat_id is Primary Key, and Auto Increment is applied.

The limit 10 takes about 0.6 seconds. The lower limit 100 takes 0.016 seconds (actually almost straightened)

The total row of tables is about 1.95 million.

Why on earth is it normal when the limit is 100, but it takes a lot of time when it's less? Also, how can I tune the query in this case?

If one of those simple tasks takes 0.6 seconds, it will definitely affect other tasks in the server, so I don't think it's going to be easy to leave it unattended ㅜ<

To add some information for your reference,

If you pull out the top 10 chat_id with limit 10 from a query that takes 0.6 seconds, it is as follows.

1826208
1649834 
1649806 
1649805 
1649803 
1647423 
1646364 
1387224 
1379463 
1379461 

The limit 100th chat_id is about 1.15 million.

If you expand with limit 100,

SIMPLE  | chat |  | ref | user |    user |  4|  const| 8193| 100.00 | Using index condition; Using filesort

It comes out like this.

If you expand with limit 10,

SIMPLE  |chat | | index | user | PRIMARY    | 8 | 2376 | 0.42 | Using where

It comes out like this.

Maybe it's related to the type becoming index instead of ref.

Is there any way to make it ref even if it's limit 10?ㅜ<

Mysql seems to have optimized itself, but I didn't know the limit count was related to the expand type.

For your information, the value of SELECT COUNT (*) FROM chat WHERE user_id=user ID is about 8500.

Help me!