11
Speed
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/10 20:28

  • Speed

  • Quite a regular

  • Posts: 310

  • Since: 2004/5/18


Interesting line of thought here. Waaaay over my head but it looks promising.

Thanks for exploring this and looking for a way to pass on to the rest of us. By you doing it, the devs can focus on the next update and learn from your experimentation. Much appreciated!
...

12
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/10 22:02

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


Ok mission on Session id hit an error, when it was primary no id can be the same and must be unique....now since removing all index's from session at first it ran much faster....and continued to...untill now it has issued me 6 times the same id, ip and update....? how and why it would do this unsure...

Yet with that makes senses of the id being monitored and unique, yet still gives me no reason why can't clear the data and still thinks i am there 6 times?

So again going to add a unique or primary index to id...yet that would mean it will stop the id being issued twice...it does that by means of MySQL will not allow it to continue...

Yet doesn't explain how an issue could be done of multiple id's the same, unless it was me refreshing....anyways mission continues....

13
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/10 22:33

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


Ok going to keep all updated on what is being tried and what works and doesn't.....

Mmmmm me love Xoops_sessions how much messing......ok....Session id and Session Ip is now a unique index...tried primary, yet slower.....

See how that fuctions, that way every ip or id must be unique, yet both are interlinked in one index...as they are always to be linked...
Better then recording the update as a seperate index....as was standard...yet shall see; report back....hopefully that is it...


Oh just to add....on the update being indexed in the slightest....if an update as we are now seeing is as the page changes, that is once only!...
So any record of this is not needed to be maintained....on the other hand the Ip is the same in the whole time the person is on the site...so interlinking that with the id, improves speed....

14
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/11 10:14

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


on security point it's like the indexing of user info, in fact feel safer now.... knowing that the user login name is linked to the pass, being needed in one index...and instance....

Then in a separate primary we have the userid, and the user information which are normally quite visible....anyways..

So I feel this reduces the security risks, as you are reducing any chance of people opening the index and finding the info...not that i even think that is possible, yet just encase...also far faster that way...

15
hooperman
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/11 15:08

  • hooperman

  • Just popping in

  • Posts: 31

  • Since: 2004/9/20


Hi Wizanda
Changed the banner MySql which now reads

CREATE TABLE `xoopscube_banner` (
`bid` smallint(5) unsigned NOT NULL auto_increment,
`cid` tinyint(3) unsigned NOT NULL default '0',
`imptotal` mediumint(8) unsigned NOT NULL default '0',
`impmade` mediumint(8) unsigned NOT NULL default '0',
`clicks` mediumint(8) unsigned NOT NULL default '0',
`imageurl` varchar(255) collate utf8_general_ci NOT NULL default '',
`clickurl` varchar(255) collate utf8_general_ci NOT NULL default '',
`date` int(10) NOT NULL default '0',
`htmlbanner` tinyint(1) NOT NULL default '0',
`htmlcode` text collate utf8_general_ci NOT NULL,
PRIMARY KEY (`bid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=2 ;

Yes i know its a XOOPS Cube table but it is only a testing site

Php admin wouldn't let me make two primary keys tho

I hope im not imagining things but yes it does appear to load faster

Any more words of wisdom will be greatly appreciated
Maybe you can get a list together of what works

16
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/11 16:33

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


Yes can give loads more in fact, we have a whole new XOOPS on our own site, all tables index's are arranged to be loads faster....

Funny though, as though XOOPS Cube is paying attention XOOPS Sphere isn't...that’s it!!!
we are becoming XOOPS Pyramid....anyone want a copy?

Kidding!

Ok, as for other things to change…. almost every tables can have multiple tasks placed in primary alone, with more fields to it, that then interlink what is needed, at said point of operation…
we have done this on our own site with many titles, and anything that would require longer to search for the data; then to have it sitting ready with the required id in the primary index….

So for instance…..
This is from mylinks categories…
PRIMARY KEY  (`cid`,`title`,`pid`)


Ok here since before these were separate one primary index…for cid and another for pid, these are now in one index, next item always needed is a title so included….

Now if you try our links or download both are done like this…once the page is originally loaded as our themes uses java cache to your browser technique…so then after first loading this, the speed of surfing in a site is faster!
As most guest do and click pages randomly…… well I do when I first visit any site, unless I know what I am looking for……… yet even still if I have to wait over 5 seconds, that will make me unsure on if to return….

17
hooperman
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/11 17:42

  • hooperman

  • Just popping in

  • Posts: 31

  • Since: 2004/9/20


Quote:

wizanda wrote:


So for instance…..
This is from mylinks categories…
PRIMARY KEY  (`cid`,`title`,`pid`)



Hee hee am gettin a bit cheeky on this
but could you tell me how to issue that command as Mysql
Thanks in advance

18
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/11 17:55

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


ALTER TABLE `xoops_mylinks_catDROP INDEX `pid

ALTER TABLE `xoops_mylinks_catDROP PRIMARY KEY ,
ADD PRIMARY KEY ( `cid` , `pid` , `title` )


Easier way is as we just did to show you this...within PhpMyAdmin, you can adjust the index's without loosing data....all you are doing is dropping an index...that gives the MySql read out, which is where we just got this from...

So if you go into the Table, then structure on that page there is the index's at the bottom...so unless you really make a mess of things, have a go in there as you can add fields to index's and test which works best...

Yet at some point will do all of these as updates for Xoops...just still in testing my self for a week or two; just testing the results of overall performance...and increasing where needed or not...

19
wizanda
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/14 11:27

  • wizanda

  • Home away from home

  • Posts: 1585

  • Since: 2004/3/21


Ok over the last few days here is the progress:


1) Text-Search-indexes of MySql indexing doesn't seem to speed things up...i.e....some of the modules...like CBB, Article using Text search for XOOPS Search.... seems to work much quicker from an id search...or an index of same information...then MySql Text index searching it...(still to try unique indexing more in concern of text information that needs quick access...yet current improvements stand we don't need this)

2) The XOOPS Session are a pain, again we have changed these ....trying to refine what the system uses and stores...as I feel XOOPS is heavy and this is one big part..(the other will explain)
We have this currently only indexing an ip address now as more work and more indexing is slower times...seems that we store a lot of information of users movements....that isn't needed to be indexed so much....

3) The Idea of putting Titles along with things is a daft one, numbers and letters sequentially used are ok and quicker...as the idea of an index is the fastest reference possible to get the data out of the table...
So in this case if a title or name is to be indexed, it must remain in it’s own index.....with the Id and what ever else is required ran as primary....so then the moment XOOPS looks for the data it knows where it goes, and what it is doing before it needs, goes a long way towards a more optimized Xoops.....

Explain that one...for instance....
A category ID is also linked to the topic ID....that is also a number for the page id for instance...these all go in primary as second fields....reason being otherwise it will look for these things each time they are asked for...that actually reduces server calls...yet adds index size...which if done well is then less load. by along way as it doesn’t have to stop and go where is this id that goes with that category before actually doing the said work of...data!....As last point that isn't indexed...and after saying Text-search-index isn't very good at referencing quicker then a normal index and a ID.....

The whole point of indexing is to save time looking for things in the information or data…..so when each template file, needs to know if it is displayed first and small points like that…accompanying these within the primary indexing…makes it that XOOPS has all needed references to run before…questioning the end data tables….instead it finds most of it in the index…and then doesn’t need to look in the data if done well…and depending what it is….

Yet on some occasions MySql is a database system and with what we have been testing I am unsure if it is me and my web site runs better the system is cleaner….and many developers are following a blind donkey…as in it does appear people copied nuke…nuke had bad tables…and we have all followed this same methodology of placing things within MySql…

Could just be me, yet do feel that my site is running a load faster not heavy any more and can take much more….and considering it has a lot of module and a lot of data….then we definitely have gone a long way from Normal Xoops…

20
yasir
Re: Fixing the Xoops Tables in Mysql to Run Loads Faster
  • 2006/12/14 13:21

  • yasir

  • Not too shy to talk

  • Posts: 109

  • Since: 2005/5/16


infact i had another idea for faster XOOPS it's:

making XOOPS client that include all user php files
so all we need from server is Data base

the user will need to install phptraid
& the site will working great

the client could gave better goods such as the goods of mesengers

info@sMicroTech.com
http://www.sMicroTech.com

Login

Who's Online

302 user(s) are online (107 user(s) are browsing Support Forums)


Members: 0


Guests: 302


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits