Another update of what we are doing…..


We decided that adding to many fields like title and things with various character with the primary is not good….so removed them….

Yet depending on the purpose and needs of a site, we did find that adding a plain index (never Text MySql index, they are slower, overall) for these, makes a huge difference….

So now in the primary index we have all needed and required reference id in one go….
So the minute MySql, is asked by our site, where or what is this from an id, there is all the information required (basically as we have written already)………..

Then in a index alone, we have added things like titles, so it doesn’t slow the overall process of finding an id in the primary….yet then is indexed in MySql in advance; rather then PHP calling an ID and then having to look for all information through a MySql Table…which when you have many modules and each is looking for a title at the same time, means a lot of calls that can be indexed in advance of it self….

This is also happening with our templates and many of XOOPS core points….as in when a template is only given an id, it also needs if it is a block or module….with what module it comes from…plus the final destination being the source….now our thought is….
If every time, MySql will find the id in the primary, yet then needs to look for these. it is a big point that can be optimized….as the moment in our own site now it finds the id in the primary with the reference id's it is going to need next…..it then sees that the relating index has the title…and so has all the info in one go and so no longer searches afterwards….other then odd points that either are hard to index like text or there is no point; as they are to long or would take longer and a lot of storage….

Anyways the search continues for the fastest CMS in the world

Hi wizanda
I have dropped quite a few extra indexes and yes indeed i can see a vast improvement on speed

Thanx for your suggestions so far

Yeh final conclusion is still sort of work in progress…..

yet I feel I have come along way with the XOOPS core, as the errors within indexing don’t need to exist to start with.

Primary = Id + any ID that it will be searched for first time it looks for anything, yet to keep this to a minimum such as ID, to make this the fastest route possible to retrieve anything, yet including anything that are key ID points needed.

Index any word that will be used a lot like Titles, Files, =Avatar files, Templates files……or Forum title as if no cache then you want retrieve the titles quickly, same applies for things like categories etc

Try and keep extra indexing to a minimum, yet extra ones that are practical interlinking, so things like user-id, date and rating for instance makes it those are then found in one go and already cached in an index in MySql.

There are tons of handy ways like this to make anyone’s site, run loads faster depending where it is needed.

Yet overall could rebuild XOOPS at the moment from some of the major ones I have been playing with like “Templates, Sessions, Smiles (my site has 200+ so setting file as index and then id as primary it instantly has those.)
Also there are number of errors that should be fixed in all of Xoops…if someone will let me know I can make MySql updates….

Text indexing is slower on testing, Unique index can be used, yet does more less what normal index will, yet must be unique each entry, those they be linked to allow it to be unique.

It is more streamlining what the system needs and indexing this rather then let it search for things in data.

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.

There’s me thinking I had finished and discovered something very strange the other night; to work what appears even faster (will need a test done on a major site of 100 users online +)

Basically, clumpness is a primary issue, it is the speed It takes in the primary id, that reaches a line it feels like in the MySql from my experimenation.
As for some reason, what I did the other night was to defy the clumpness theory, and add multi-fields in a single index; still along the lines of the last post, just not in primary as I first started, which is a cause of clumpyness.

Primary = Id or the fastest route to the number that XOOPS asks for to then find the rest of the line it came from.
Index = Titles alone as to be a fast index of titles for displaying on quick changes or on a single item, index files name.
Index = Cid, Topicid + now also, all fields of one or two digits that are required by something in the same index, so also APPROVED, hashtml, hasattachsignture.

That can make a quite a few fields, in something like newbb forum posts, yet this is where it was tested to begin and how I found out.
As when I place the items in individual indexes of the same data it makes two problems:

1) the size of the indexing is much larger then the data; that is not Good practise.
2) Since it is larger, and more indexes are needed to be looked at to place the same data, it takes longer and due to the size of extra indexing can slow a whole site down with it.

So now we have just 3 indexes in bb_posts the primary id being post-id, and then a interlinking index with all variables (single or a few digits) for any post and id’s of that post in one index together. Then finally the subject in an index alone for faster placement of titles.

To me that goes so much faster, yet with this bit am unsure why that would work, as my logic would state separate indexes quicker find, it doesn’t seem to be like that.
When it finds the primary ID to begin, it is given that a line of any other indexes, now if in that other index you have all need variables also required, when it first assigns where it belongs, it appear to also collect all of that and so vastly reduce loading times.

Ok final conclusion is this, you can ignore or read most of the rest, some is ok and all is tested…..Yet now following all tests, here is my quick conclusion.

The shortest path possible to the information is all that is required.

This normally means just a primary ID, with a sequential sequence in it.

MySql indexing is not for storing information; it is for reaching the line that exists in a table.

By storing lots of information in MySQL Indexing you make it top heavy and so a slower site……This is as it can’t reach the information, without first checking each index for if the information exists…….

Reach the line through primary index first, so it sees all information in one go….If you add a second index it also has to check this then, before reaching information, so then slower.

sorry for my bad english.

you can modify groups_users_link...

groups_users_link is not a data table is a relational one. this table it use to make relation between group and user. Then you just have groupid and uid in.

Actually in this table you have a linkid sequential column, and this column was the primary key. After you have an index with groupid and uid. First, there is a fonctionnal mistake in this index because is not define on unique index.
Second, what is interest of linkid column????

On this kind of table you just have only the key of the twice table. Is enough.

In this table you just have 2 column groupid and uid. Just this.
And you may put the twice column in primary key.

i Am agree there is no speed interest in this primary key with all column of table, but you have an interest on this data must be Unique.

By the way, we gain place because we have less index. then whe gain speed on update/insert/delete on this table, gain speed on select because the data is smaller etc...

Quote:
L'intérêt c'est d'avoir des identifiants uniques qui ne rentrent pas dans la logique métier.
C'est normalement plus souple pour les 'évolutions'

PS : tu peux aussi te passer de base de données, c'est encore plus rapide...

English Google Translation:
Quote:

??????

Desole je comprends pas ton intervention.

Je vais le dire en français. desole pour les autres

Cette table n'est la que pour faire la relation entre le group et les user. C'est betement une table de relation ce que l'on fait classiquement en relationnel. Elle n'a besoin que et uniquement des cles primaires des deux tables qu'elle met en relation.

=> linkid ne sert a rien fonctionnellement (c-a-d en terme de logique metier et meme de souplesse???) et techniquement, il prend de la place pour rien, et en plus ne force pas l'unicite des donnees (ce qui est dommage).
Et je vois pas en quoi ajouter un index primaire autoincrement sur ce genre de table de relation ajoute de la souplesse et de l'evolutivite...

Il me semble pas avoir dit qu'il ne fallait jamais utilise ce genre d'index autoincrement... (qu'on appelait cle techniques de mon temps qui sont justement tres pratique dans ce genre de table de relation car du coup on a que 2 champs si on avait du coller le nom, surnom etc... du user pfuuut)
Donc desole je comprend pas ton intervention... et j'ai beau relire mon intervention precedente je vois pas ce que me vaut cette intervention... et cela meme si je suis pas super bon en anglais.

Et perso j'adore les bases de donnees... je suis plus specialiste Oracle (de la version 5.0 a 8i apres bcq moins) que MySQL, et le relationnel (dans les bases ) ca a longtemps ete mon dada voir meme ma specialite .
Quand a la rapidite... plus tu auras de donnees et plus l'interet d'une base de donnees sera importante. Si tu as 5 donnee a gerer utilise un fichier plat c'est sur que cela sera plus rapide, si tu en as des tonnes y a pas photo prend une base de donnees (et si tu en as vraiment vraiment bcq y a des moteurs plus performants que d'autre...)

English Google Translation of the above:
Quote: