How to optimize this SQL statement: SQLcodeselect * fromdx_gd_goodswhereg_label & gt; = 100101000andg_label & lt; 100102000orderbyg_likenumDESClimit
SQL code
select * from dx_gd_goods where g_label >= 100101000 and g_label<100102000 order by g_likenum DESC limit 30
The preceding SQL statement takes 2 seconds to run. It's too slow.
If so, the actual requirements must be sorted.
SQL code
select * from dx_gd_goods where g_label >= 100101000 and g_label<100102000 limit 30
After doing so, the program only needs 0.16 seconds to return
After sorting is completed, the execution time is short.
------ Solution --------------------
G_label and g_likenum are both indexed.
------ Solution --------------------
I think your test is not accurate, and there is no reason for the difference. Have you obtained the average value for multiple running times?
------ Solution --------------------
G_likenum no index used
------ Solution --------------------
The execution of the previous statement is slow, and the execution of the latter is fast. I guess the reason is this: when scanning data records, the latter finds records that meet the where condition, just make up 30 items. even if g_label does not have an index, there is no problem as long as the proportion of qualified records in the total dataset combination is not very low, it must find all the records that match the where condition and then sort them by g_likenum to retrieve the first 30 records. if this process does not have the help of proper indexes, it is likely to cause a full table scan.
The solution is to set an appropriate index. However, what indexes are appropriate depends on the data distribution characteristics in the dx_gd_goods table. For example, if the proportion of records that meet the where condition is high enough and the differentiation of the g_likenum field is also high, indexes such as (g_likenum) are good; however, if only a few records meet the where condition, indexes such as (g_label, g_likenum) may be better.
In addition, sometimes databases (such as mysql) may not necessarily use indexes as you think. Therefore, you must use the explain statement to investigate the index. if necessary, you can use index hint or other methods to intervene.
--------------------------------
Based on the plug-in extension function provided by the CSDN forum, I have made a signing tool and shared it with you. we welcome Technical Exchange :)
------ Solution --------------------
Is g_likenum a type of centralized data? Enum?
------ Solution --------------------
Is this an SQL statement from the front end? If yes, you can create an index for g_label and g_likenum. What is your mysql version? If yes, create a partition.
------ Solution --------------------
Discussion
Haha,
Fortunately, the index was forced just now, and the speed was acceptable. it would return in 0.7 seconds.
Although not very good, but still can accept reference:
Is this an SQL statement from the front end? If yes, you can create an index for g_label and g_likenum. What is your mysql version? If yes, create a partition.
------ Solution --------------------
Create a joint index on g_label and g_likenum
------ Solution --------------------
Discussion
A little big
------ Solution --------------------
Discussion
Long brother,
I have three such statements,
[Code = SQL]
Select * from dx_gd_goods where g_label> = 100101000 and g_label <100102000 order by g_likenum DESC limit 30
Select * from dx _......
------ Solution --------------------
Discussion
Oh,
I have not yet created a combination. I created it separately.
Reference:
Reference:
A little big
Can you create a partition? you have already created a composite index for the two fields. I don't know if the partition creation effect is obvious, but you can try it first, if there is no big effect, delete the partition.
------ Solution --------------------
This is the only way to do it. optimization is a big action.
