MySQL vs. SQLlite - performance

[yyrkoon]

I was wondering if anyone knows if one outperforms the other. I would assume MySQL is more powerful and would outperform SQLlite, but we all know what they say about assumptions..

The reason why I ask is I am more comfortable with MySQL and i have an existing database from an nwn1 world that I would like to use for my nwn2 pw.

How hard is it to convert from an nwnx2 system to the new nwnx4? Is it worth the effort, or should i just go with a new MySql install or even just use the SQLLite?

any feedback is appreciated.

[Fireboar]

You don't NEED a new MySQL install for anything - I'd stick with MySQL and just use a different database name if I were you.

For smaller databases, SQLite tends to outperform MySQL, but the opposite seems to be true for larger databases. You also miss out on features such as DELETE...LIMIT and ALTER syntax, so I'd go with MySQL if you've already got a MySQL server running.

[yyrkoon]

So, if i have a MySql instance running with all my tables I am already familiar with, shouldn't i just point this at my existing db?

I guess I should say how do i do that? i assume i just change the nwnx4 ini to use the MySql and then change the Mysql.ini to point to my database... which i understand at a high level, but do not really know how to do it... guess i need to roll up my sleeves more and dig in.

I would assume then most of my existing db interaction script will work fine in NWN2, i just need to redo my conversations and change/create the items to match resrefs where applicable...

again, any help is appreciated. I think my major issue is my brain is toast by the time i finsh a day of database work and programming

[Fireboar]

Oh right I see... so you're just using the same data on a different module? Well I can't really see the benefit of doing so since characters and areas and stuff can't be ported between the two games, but it's entirely possible - just as you say - point to the existing database in the ini file.

I'd make a backup first though, if I were you.

[yyrkoon]

I do not want the same data, just the existing structures. I may end up just starting for scratch though and porting ove code snippets. Seems we use some custom version of the nwnx include so i may just use the ones that come with nwnx4 and add any additional ones we have.

I messed with this for about 2 hours last night, not too happy with my progress.

[Fireboar]

Hm, if I were you I'd use a tool like phpmyadmin to export the database with the "Structure only" option. Either that, or you could copy your entire database to a new location and perform TRUNCATE queries on each table.

[Kres]

Greetings, First of all SQLite can not do multiple instances. at KoW (Kingdom of Winterhaven) we use a merged player vault and point to the same database on a box that is not on either of the game servers, we currently run 3 game servers (2 live 1 dev) off a shared DB, we even have a DEV db that is isolated from the others, all you have to do is point the nwnx.ini file to the right schema you want and your done, share the vault folder on a network drive and point to it. Named Pipes is the recommended method (for performance).

I do not want to get into extreme details due to our personal network protection, but you should understand the jist of what I am saying.

[yyrkoon]

this sounds like a good idea. We had a simular setup for NWN1. I am a bit confused as to if i have made the correct entries when converting from nwnx2 (from the nwn1) to nwnx4 fopr my nwn2 environment. looks like nwnx4 uses several files to accomplish what was a single file in NWN2.

I also noticed my Nwn1 world used a custom include for the nwnx2 and does not include the nwnx_include file. I plan to go though the 2 and consolidate them, or maybe remove all duplication and keep my custom methods in the custom file.

Has anyone else done a conversion from nwnx2 to nwnx4? is there a guide or thread about this?

[yyrkoon]

well, i sorted this all out. It appears we use a username that does not have full admin rights to the db, so you cannot create tables and such dynamically. This was giving me an error and I have worked it all out.

thanks for the help.