| Re: Fixing the Xoops Tables in Mysql to Run Loads Faster |
| by Garrath on 2007/11/4 15:59:10 ?????? 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: Sorry I do not understand your response. |
| Re: Fixing the Xoops Tables in Mysql to Run Loads Faster |
| by hervet on 2007/11/4 15:25:01 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: The interest is to have unique identifiers which are not in the business logic. |
| Re: Fixing the Xoops Tables in Mysql to Run Loads Faster |
| by Garrath on 2007/11/4 13:14:36 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... le="color: #000000"><?php //Suppression de l'index ALTER TABLE groups_users_link DROP INDEX groupid_uid; //Suppression de la cle autoincrement ALTER TABLE groups_users_link DROP linkid; //Creation de la cle primaire sur groupid et uid. Elle force l'unicite des lignes. ALTER TABLE groups_users_link ADD PRIMARY KEY (groupid, uid );
|
| Re: Fixing the Xoops Tables in Mysql to Run Loads Faster |
| by wizanda on 2007/10/31 8:44:19 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. |
| Re: Fixing the Xoops Tables in Mysql to Run Loads Faster |
| by wizanda on 2007/3/10 8:30:21 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. |
Search
Recent Posts
Who's Online
Donat-O-Meter
| Stats | |
| Goal: | $15.00 |
| Due Date: | Jul 31 |
| Gross Amount: | $0.00 |
| Net Balance: | $0.00 |
| Left to go: | $15.00 |
|
|