About MySQL table with large text limit slow optimization

Problem Scenario:

Commodity table goods, there are several large text fields, type with the Mediumtext, table records more than 280,000. Paging query with limit, on the average PC needs about 12 seconds, this order of magnitude of data should not be so slow.

Cause Analysis:

Several large texts slow down the query speed, if only the fields of the primary key or number type are checked quickly. Some people say why do you check the big text? The reason is that product managers and designers agree that pagination should show some of the summary is more beautiful.

Solution:

1. General Practice: Each paging query, provide a start ID, fast.

Select *from goodswhere id>65310limit; 

2. Workaround: First query out a page of the primary key ID, and then query in the statement, the speed is slightly slower, the performance of the main loss on the ID; However, it is convenient and minimal for existing program changes.

Select *from goodswhere ID in (SELECT ID from                     goods        limit 63660,20< c9>) TT)     

As to why a layer of temporary tables is wrapped in the in statement, the MySQL syntax restricts the use of limit in the in statement.

PS: Because of the privacy of the protection project, the above query uses asterisks.

Transferred from: http://fenglongsheng.com/post/10270.html

About MySQL table with large text limit slow optimization