List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:May 12 2001 4:47pm
Subject:Re: MYISAM v.s. GEMINI Tables
View as plain text  
Bonjour Warren,

  What  about  InnoDB tables ? Could you change the TYPE to INNODB and
  run  the  same  test ? This would be great to compare MYISAM, GEMINI
  and INNODB.


WvdM> Goodday to you all

WvdM> For those that are interested, I have managed to get my GEMINI tables
WvdM> working successfully, and changed one of my reports to use the MYSQL tables
WvdM> instead of my original MSACCESS ones. Had a couple problems with the initial
WvdM> altering of some tables from MYISAM to GEMINI, but managed to do some work
WvdM> arounds and get it right eventually. I set up a test, got four tables, they
WvdM> are linked in pairs as the set's contain different info. I am using a inner
WvdM> join statement, joining on a indexed field. (select * from shcoslin inner
WvdM> join shcosmas  on shcoslin.batch=shcosmas.batch) This is not my exact
WvdM> statement as there is also a where clause, but just to give you some
WvdM> details. Once I have completed filling my grid with the above statement, I
WvdM> then do it again but on two different tables. My timer runs from the
WvdM> beginning to end, i.e. the time taken includes adding the neccessary rows to
WvdM> the grid, and so it is not only the execute part.

WvdM> My table details are:

WvdM> shcosmas = 3766 records
WvdM> shcoslin = 78432 records
WvdM> shinvmas = 6057 records
WvdM> shinvlin = 20314 records

WvdM> the two "shcos" tables are fixed length, about 200 characters
WvdM> the two "shinv" tables are dynamic, and mostly about 200 characters as well

WvdM> This test was not for inserts, purely select statement as per above. My
WvdM> inserts I am not too worried about as these are mostly spread out and never
WvdM> more than 50 inserts consequetivelly, normally max 5. Therefore I am more
WvdM> worried with the reporting hence this test.

WvdM> I ran each test 6 times on each setup, I know this is not a huge amount of
WvdM> testing but all 6 showed the same times bar one second, so I figured 50 will
WvdM> prove the same results.

WvdM> Results:-
WvdM> About 6000 records selected and inserted into the grid, then sorted.
WvdM> 47.5 seconds
WvdM> About 13000 records selected and inserted into the grid, then sorted.
WvdM> 145.5 seconds

WvdM> Then once changed to GEMINI format
WvdM> About 6000 records selected and inserted into the grid, then sorted.
WvdM> 21.0 seconds
WvdM> About 13000 records selected and inserted into the grid, then sorted.
WvdM> 100.5 seconds

WvdM> As said above, these times include the time taken to add the rows using a
WvdM> while, wend statement to a grid in my application. The timer is at the exact
WvdM> same point, and is logged into a file therefore there is no chance of me
WvdM> starting/stopping a clock watch at the wrong time, etc etc.

WvdM> I am running this on a PII600 Laptop, running 256MB Ramm and Windows 2000
WvdM> Professional using Nusphere's MYSQL for Windows which has the gemini table
WvdM> type.

WvdM> So, the above results makes for VERY interesting reading. I know the GEMINI
WvdM> table is still in Beta Testing and so with that, does come some
WvdM> complications which I hope will be sorted out once the official release, but
WvdM> now that it is running I will need to see if I have any other problems.

WvdM> All my data was imported from a MSACCESS database, and found that I had to
WvdM> import the table definition first, change the type and add the neccessary
WvdM> indexes, and then import the data otherwise the mysqld-opt crashed
WvdM> occassionally.

WvdM> Regards
WvdM> Warren

WvdM> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WvdM> Warren van der Merwe
WvdM> Software Director
WvdM> PRT Trading (Pty) Ltd t/a RedTie
WvdM> Durban, South Africa
WvdM> Cell (+27-83) 262-9163
WvdM> Office (+27-31) 767-0249

MYISAM v.s. GEMINI TablesWarren van der Merwe12 May
  • Re: MYISAM v.s. GEMINI TablesBAUMEISTER Alexandre12 May
  • Re: MYISAM v.s. GEMINI TablesJeremy Zawodny12 May