Of the train of thought distributing a page in MySQL inquiry optimize
Tuesday, March 03, 2009 by rain
The person that talks minute of page it seems that is very few,everybody indulge Limit M, n? Below the case that has index,Limit M, n speed is enough,But be when complex condition search,Meeting search of Where Somthing Order By Somefield+somefieldmysql alls over a database,Find out " all " the record that accords with a condition,Take out M next, n record.If your data bulk has hundred thousands of,The user searchs a few very common terms again,Want next ordinal read finally a few pages revive an old dream.Mysql this very solemn and stirring ceaseless operation hard disk.
So,Can try to let Mysql also store minute of page,Want a program to cooperate of course.(here just offers a tentative plan,Welcome everybody to discuss together)
The cent page of ASP:There is Recordset object to realize minute of page in ASP system,But mass data is put in memory,And do not know when ability invalidation (ask ASP ace to show) . SQL database distributes a page:With memory process + cursor means distributes a page,Specific implementation principle is not very clear,If the tentative idea inquires the result that gets need with,Or Id collect,When needing follow-up page, want to numerate according to the IDs in the result only relevant record.Want very little space to withhold all IDs of this second inquiry only so. (How doesn't the inquiry in SQL know to be clear about as a result expire rubbish? (How doesn't the inquiry in SQL know to be clear about as a result expire rubbish??
Such,Can let Mysql imitate memory divide page mechanism:1. Select Id From $table Where $condition Order By $field Limit $max_pages*$count; inquires the IDs that accords with a condition. Demarcate is the biggest the record amount that accords with a condition,Also need not add.2. Because Php is after executive end,all variable want Lost,Can consider so: ?addle ?. Build in Mysql express temporarily,Inquiry uses a time as a result or random number is inserted as only sign.Build Page1~pagen among them field,Every field saves the Ids of the need in this page, a such Id are recorded to. Plan B. If open Session, in also can be being put in Session, save,It is to be put in the file to save actually.Build array of a $IDs,$IDs[1]~$IDs[$max_pages] . Consider occasionally the user can open a few windows to inquire at the same time,Should make an only mark for $ids,Avoid to inquire the result is enclothed each other.Two dimension group and $$var are tweak.3. In the request of every page page,The IDs that finds correspondence directly,Among with " , "Removed:Speed of Select * From $table Where Id In ($ids); is very fast 4. What ending should consider to inquire a result is automatic cleared,The setting times or can be clear about randomly in proportion.If use Mysql to be expressed temporarily,should increase field of mark of a time,$IDs["time"]=time(); should be joined to be not operated later in proper time in Session regard as expire data.
5.If want to optimize,Can consider to use 1 with 2.a medium statement incorporates Select. . . . . . Into. . . .
Note:1. Above just is aimed at Mysql revamp plan,Hope Mysql where day can add these functions 2. Other database is OK also apply mechanically.3. If other database still has the more advanced kind that distribute a page,Tell I or Mailto please: Steeven@kali.com.cn4. If have very much data really,should inquire,Still mix Mysql good-bye,Sql, oracle offerred more advanced keyword index to inquire.
Excelsior,Above is cast a brick to attract jade-offer a few commonplace remarks by way of introduction so that others may come up with valuable opinions only,The welcome discusses the issue that distribute a page jointly.(also but about other database) the hope can arrange all sorts of means that distribute a page one day consult for the novice.