Why is mySql sometimes slow when querying ??? In this way, a shopping site collects tens of millions of data records in dx_gd_goods and creates indexes by category. after the where root category ID and query, at the beginning, it was a little slow, and the subsequent query speed was still relatively fast, but it was not long, and it was very slow, it was very slow, and mysql times out after the slow, data cannot be queried. When I use explain to view information, I use an index. why is mySql sometimes faster and sometimes slower ???
Yes.
A collection and shopping site stores tens of millions of data records in dx_gd_goods and creates indexes by category. the root category ID after the where clause is slow at the beginning of the query, the next few queries are still relatively fast, but the query speed is not long. the query speed is very slow and slow. mysql times out later, and no data can be found.
When you use explain to view information, the index is used. the query type is range.
Later, I wanted to split the database into partitions by category. After partitioning, the first seven or eight queries started were still very fast. if the query continued, the problem was the same as above, mysql timed out, no data is returned.
When you use explain to view information, partition is used. the query type is all.
My mysql version 5.1;
I don't know how to solve this problem. I can't solve it now.
------ Solution --------------------
SQL statement
------ Solution --------------------
Does t_id have an index? What is the engine...
This SQL statement should not be slow.
------ Solution --------------------
It seems like partitioning is causing a disaster. t_id should not be slow after indexing. you can test it more and you will know.
------ Solution --------------------
Explain partitions select *... release the structure for a look
------ Solution --------------------
T_id> = 100101000 and t_id <100102000
Index should be created on t_id
Partition by t_id