On 29 November 2012 19:40, imacat <ima...@mail.imacat.idv.tw> wrote: > Jan, > > On 01.11.30 02:19am, janI said: > > Thanks I did not know that LEFT JOIN and JOIN has different speeds on > inodb > > tables. I made the fast fix, and split xmaint_uids into smaller tables, > and > > is running now. > > > > BUT I am dead nervous of deleting wrong data !! so the review in general > > would be nice. > > > > I have also decided to post the user list here, to give people a chance > to > > shout and get removed from the list. There are very little margin for > > errors on our database, and I would not like to cause problems (I think I > > already have used this years allowance). > > > > Thanks for your suggestions, I will implement a couple of your ideas > before > > I release the final script. > > Thanks for this tremendous work here. I'm still fighting with my > class and research project at 3:00am. ^^; > > This server is a virtual machine. There is a limit to its CPU > power. And Andrea's suggestion is right. Join operations actually > creats a big n times m table first and then filter. That sucks when n > and m are both large. > I thought that happened with both JOIN and LEFT JOIN....Andrea suggested simple JOIN. I always avoided using JOIN, used memory tables instead, but the VM is not powerful enough to handle that.
Which kind of research project do have ? going for a MBA or more ? Jan. > > > > > Jan. > > > > > > > > On 29 November 2012 19:09, Andrea Pescetti <pesce...@apache.org> wrote: > > > >> On 29/11/2012 janI wrote: > >> > >>> ooo-wiki VM is so weak (compared to my notebook) that a lot of my > >>> "special" > >>> select/join statements timed out...and the just because I have a simple > >>> where clause "where user_id in (select user_id from wiki_maint_uids)" > >>> > >> > >> I only had a quick look yesterday and I cannot check now, but there were > >> several possible speed improvements, including using a simple JOIN > instead > >> if a LEFT JOIN and then discarding NULL matches, introducing a primary > key > >> or an index in the wiki_maint_uids table and using update on joins > instead > >> of subqueries. I didn't test any of these, and I didn't see the > database, > >> so don't trust the lines above too much! They are just semi-random > thoughts > >> based on the code I saw yesterday, and it could turn out that these > changes > >> slow down the queries instead of improving them. > >> > >> Regards, > >> Andrea. > >> > > > > > -- > Best regards, > imacat ^_*' <ima...@mail.imacat.idv.tw> > PGP Key http://www.imacat.idv.tw/me/pgpkey.asc > > <<Woman's Voice>> News: http://www.wov.idv.tw/ > Tavern IMACAT's http://www.imacat.idv.tw/ > Woman in FOSS in Taiwan http://wofoss.blogspot.com/ > OpenOffice http://www.openoffice.org/ > EducOO/OOo4Kids Taiwan http://www.educoo.tw/ > Greenfoot Taiwan http://greenfoot.westart.tw/ > >
