Boss, the following sentence SQL, the execution time has reached 6s, what should I do?
in the end, I chose the scheme of "create a separate index" with the least impact.
not sure this is the best result! just because of my environment, comprehensive testing is more difficult, so I chose the "fastest, least impact" approach.
thanks again to @ Yujiaao and @ chenbinhua for their help.
Business scenario
sort by code > click_count , and get the first 20 results with c_id as 17 + choice as 1 .
SQL original sentence
SELECT `id`
FROM `akb48`
WHERE `c_id` = 17 AND `choice` = 1 ORDER BY `click_count` DESC LIMIT 20 OFFSET 600;SQL analysis
"1"SIMPLE"akb48" N "index"IDX_C_ID,IDX_C_ID_POINT"IDX_CLICK_COUNT"4" N "10281"0.61"Using where"
select_type: SIMPLE;
type: index;
key: IDX_CLICK_COUNT
key_len: 4
rows: 10281;
filtered: 0.61%;
add:
table data 600,000 items , visual observation is because ORDER BY takes the lead in execution? Causes other indexes to be unavailable.