One of my mysql database tables there are nearly 600000 data, is paging display on a web page, a page display 50 data, display speed nearly a minute, the great god, please help to look at, more reward points must be
My SQL statement is: select independence idx, namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao where date_sub (curdate (), interval 6 month) & lt;=the date (attr2) order by attr2 desc
Attr2 is time field, behind the where condition is to display the data in this table for nearly 6 months,
My table structure is:




I give the table a index is:

CodePudding user response:

Date_sub (curdate (), interval 6 month) & lt;=the date (attr2) into attr2>=date_sub (curdate (), interval 6 month)

CodePudding user response:

Index failure condition columns cannot function

CodePudding user response:

Through the explain SQL statements, view the execution plan

CodePudding user response:

refer to the second floor with ah response:

condition columns cannot function, or index failure

The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao order by attr2 desc
Now I do not add any conditions, now the table below was only 40 ten thousand data, should not be so slow query, all information and you won't come in a minute

CodePudding user response:

The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao order by attr2 desc
Now I do not add any conditions, now the table below was only 40 ten thousand data, should not be so slow query, all information and you won't come in a minute

CodePudding user response:

The content of the equipment performance, network bandwidth and storage length will have effects

CodePudding user response:

You this check slowly, the estimate is a full table scan of reason, don't walk index because you use the date (attr2);
If attr2 is time type of data, then this column should not be longtext types, find a time to convert the column type to a datetime, so you don't have to use the date in SQL to transformation type, also can use attr2 indexes;

In addition, had better put the execution plan, and explain how much is accounted for, you need to query data such as your table data is 600000, so how much you want to query data for six months is?

CodePudding user response:

The

reference 7 floor by the stars of a cold reply:

I'm SQL write so now
The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao where attr2 & gt;=date_sub (curdate (), interval 6 month) order by attr2 desc

Lanmu_zhaobiaogonggao 439539 data in the table, nearly six months of data is 397661

The execution plan is:


Lanmu_zhaobiaogonggao this table index is:

CodePudding user response:

Explain to see whether or not to use the index

CodePudding user response:

refer to the eighth floor qq_16910903 response:

Quote: refer to 7th floor by the stars of a cold response:

I'm SQL write so now
The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao where attr2 & gt;=date_sub (curdate (), interval 6 month) order by attr2 desc

Lanmu_zhaobiaogonggao 439539 data in the table, nearly six months of data is 397661

The execution plan is:


Lanmu_zhaobiaogonggao this table index is:

From the point of execution plan, it's no use your SQL to the index, so deal with questions about attr2 column type, want to use the index, can't use the date of attr2 function transformation,

CodePudding user response:

The field type can small as far as possible,,,,,,,

CodePudding user response:

I behind the where clause is a attr2 fields, nearly six months at the moment, I query data according to 90% of the total, if the problem is I now remove the attr2, 100% directly from a visit to get all the data in the table, now is 441385 data in the table why would be so slow, so can't walk without a fundamental index that line, to how to solve it?
The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao

CodePudding user response:

For example, when you turn over a book need to look at the 90% of the content again, then the book catalog meaningful for you?
A table with so many longtext type, and the sorting is longtext fields, fast up,
Narrowing the scope of data, reducing the need to display the columns, after the paging read out again, with date, or the primary key to sort it,
Sometimes light is bad optimization from the technology, need to negotiate and product demand

CodePudding user response:

1, you now statement:
The select independence idx namex, parentx, sj, paixu, filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx from lanmu_zhaobiaogonggao order by attr2 desc
Such in general is the use of index of less than, if is mysql8, suggest in "attr2" add descending index, you can see in the explain plan using the index,
2, this can reduce the data type of the field to reduce as far as possible, at the start of your statement with date (attr2), so if you can consider to add a date (attr2) virtual columns, use normal index on virtual columns, when as query conditions, believe that speed will be improved

CodePudding user response:

Attr2 column is longtext field type, memory will be big, check the execution plan, whether to use the disk, so you can appropriate tuning sort_buffer_size

CodePudding user response:

 

Select 
Independence idx, namex parentx, sj, paixu filex, attr0, attr2, attr3, attr4, attr5, attr19, specialx 
The from 
Lanmu_zhaobiaogonggao 
Where 
Attr2 & gt; '2019-02-07 00:00:00' - the date in the program computing 
The order by 
Attr2 desc; 

- attr2 field type datatime, or timestamp (int) 

- there has been a index 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull