24
Ok after much playing around and still more adapting going on, as see changes happen to the sites performance, which can take a few days to process and accumulate caches etc for full results.
Decided now, to much in one index is bad and causes clumpyness, as in:
if primary = id alone, that calculates a unique id each time,
if primary = id + name (for instance), the time taken for the original assessment of where to find the data from the id, is then slower.
Also a primary if issued with two numerical values; MySql and any item unique or primary allows, it can work sequential in one of the fields, as long as one number is unique each time; So if you have two numerical values as primary, these can go out of sync.
So by primary = id alone
The index = name
And a index = all Id’s
And a index numerical values required for viewing in separate indexes.
That is faster.
If we also have bid, mid, cid, lid etc
Primary = lid or main ID cause of that table, one id and fastest possible to find a unique id and to then find the required information. (both numbers and alphabet used, as numbers and anything sequential works faster, minimum sequential events is best.)
Then index, all items that are required allot… now I know I started to say blocking items together in an index is good, yet it is also slower depending how many times the item is needed and what it is required for. Plus if it is a name or a sequential order, as mixed items sequential and name is ok, yet two sequential values if unique, doesn’t stay that way always.
For instance in our links etc when lid is this and cid is that, yet also cid is required independently as a number for other fields and index’s.
Then first it looks for lid (for any link), this it finds in primary, then from primary which is fastest, it see a second index along side and links to its category.
Now if certain fields are interlink across different fields also, then by placing each individual cid in each of those in it’s own index, then this means that it can cross reference its self across, for seeing how many of each it will need.
Now though before, I was quite happy adding names to indexes of sequential value, for the sake of speed. It is also easier for an index of names, to be just that alone. Then as the primary cross references that whole field, it sees all the other indexes in it as one.
Yet when these are clumpy which is to place, two sequential numbers or items next to each other, these eventual slow down tasks.
As in submitting etc, as before it can find the item to be added from an id, first it must find its way through the indexes that take place, with field names next to that of numerical id, if it is separated the two processes can work independently to each other.
I hope I make sense, I know this is going on and is complex, yet so is MySql indexing and the effects that can be achieved.
A good couple of things found in XOOPS lately, is things like files for smarty, by doing this whole process of none clumpyness.
Primary id = id
Index = refid
Index = file name
That then searches primary and find things quicker, the more extra index’s you add for no reason of approximated data, the slower the first indexing will be.
So by keeping indexing short and tidy we also speed up all processing of any XOOPS site.
Though when any items number is needed, we can gather this through smarty calls debug and MySql in Xoops, as it shows you what tasks are being asked for from MySql each time, before any page can be displayed; so by filling in all of these that are missing, and removing clumpyness XOOPS goes 5 times faster.
