[Steve M.]

Hey guys. So what's better to use, SQLite or MySQL? And by that I mean what to use when your server has UCP or some other things on web host which is somehow connected with the server.

[T0pAz]

I would say MySQL. Here is a small quote

Quote:

Originally Posted by Justin SQLite is great for testing and prototyping, or for embedding in applications. MySQL is worthy of (large scale) production environments. Here is my personal summary: SQLite: easier to setup great for temporary (testing databases) great for rapid development great for embedding in an application not fit for production (in large scale) doesn't have user management doesn't have many performance features doesn't scale well. MySQL: far more difficult/complex to set up better options for performance tuning fit for a production database can scale well if tuned properly can manage users, permissions, etc.

[wups]

Use mysql for the option to connect remotely(show stats on a webpage or manage user stats etc.). SQLlite doesn't have that.

[wups]

Quote:

Originally Posted by ****** That, too, is simply false: http://uk3.php.net/manual/en/book.sqlite.php

How can you connect to it, if sql lite doesn't have any running process (when the server is off)?

[DonWade]

So for the begginers SqLite is easier to learn ?

[Steve M.]

Thank you ****** so much. So if I want to connect to my database file remotley I just enter the IP address and the path to my DB file on that host? What I mean is: http://69.23.52.44/folder/file.db

[JoxyBullet]

http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
Check these for remote access.

[[HiC]TheKiller]

I only personally use MySQL for when I'm connecting something like a website to the database too as it is easier. MySQL is harder to set up if you're just using it as a simple user base. SQLite is good if you do not plan on having a website etc.

[Calgon]

I always prefer MySQL if I know I'm going to remotely access the data, I know you can remotely access SQLite databases through PHP and other methods, but you generally need to give the remote server access to the SQLite file (unless I'm missing something) which is more trouble.

Quote:

Originally Posted by DonWade So for the begginers SqLite is easier to learn ?

It's SQL which you'd need to learn. But it's relatively easy to get a beginner's grasp on.

[linuxthefish]

Quote:

Originally Posted by Steve M. Thank you ****** so much. So if I want to connect to my database file remotley I just enter the IP address and the path to my DB file on that host? What I mean is: http://69.23.52.44/folder/file.db

It generally involves running a webserver and samp server on the same system.

[Kyosaur]

Quote:

Originally Posted by ****** I'd say SQLite for exactly the same reasons as those listed above! You are not running an enterprise server system distributed over hundreds of nodes with thousands of connections. You are running a SA:MP server, at that scale the features provided by SQLite are all you'll ever need...

I respectfully disagree, and would say MySQL is the better choice.


MySQL is faster, better with huge loads, the modern plugins support threading, and web integration is extremely simple with PHP. The only reason i can think of to use SQLite is because it requires no setup, and this may be easier for new scripters. Still though, most servers already have access to mysql servers from hosting companies/their websites... and if they for some reason dont, there are tons of tutorials and tools available to make this simple. The only thing that is left would be installing the plugin, which if they cant do, they have more important issues to worry about.

Edit: It sort of becomes a battle of simplicity vs speed i guess. I'll always go with the method that is the fastest personally, even if that means taking a few more steps for setup (its a one time thing :P). I can understand you're a library writer though and want as little work as possible for the end users.

[Calgon]

Quote:

Originally Posted by Kyosaur I respectfully disagree, and would say MySQL is the better choice. MySQL is faster, better with huge loads, the modern plugins support threading, and web integration is extremely simple with PHP. The only reason i can think of to use SQLite is because it requires no setup, and this may be easier for new scripters. Still though, most servers already have access to mysql servers from hosting companies/their websites... and if they for some reason dont, there are tons of tutorials and tools available to make this simple. The only thing that is left would be installing the plugin, which if they cant do, they have more important issues to worry about. Edit: It sort of becomes a battle of simplicity vs speed i guess. I'll always go with the method that is the fastest personally, even if that means taking a few more steps for setup (its a one time thing :P). I can understand you're a library writer though and want as little work as possible for the end users.

Actually, I think SQLite is faster in SA-MP than the MySQL plugin is for executing queries.

[Kyosaur]

Quote:

Originally Posted by Calgon Actually, I think SQLite is faster in SA-MP than the MySQL plugin is for executing queries.

I ran some benchmarks a couple months ago in a previous thread, but there's always the possibility im missing something that makes the test invalid or unfair. Here's the quote from the previous thread. I do encourage other people to run their own bookmarks, possibly with more statements (i only used the 3 most common).

I really only learned enough SQL to get by, so my knowledge of optimizations/engines isnt as good as it SHOULD be. There may even be ways to speed up MySQL as well, im not entirely sure. Either way though, with the option to thread queries (i didnt do so in these tests) i think MySQL is the best choice.

Quote:

Originally Posted by Kyosaur Code: [20:54:29] SQLite test starting... [20:54:42] SQLite Insert: 100 queries in 12684 ms [20:54:42] SQLite Select: 100 queries in 25 ms [20:54:42] >> SQLite Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [20:54:56] SQLite Update: 100 queries in 13692 ms [20:54:56] MySQL test starting.... [20:54:56] MySQL Insert: 100 queries in 35 ms [20:54:56] MySQL Select: 100 queries in 39 ms [20:54:56] >> MySQL Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [20:54:56] MySQL Update: 100 queries in 133 ms Here is the script i threw together for this test. Keep in mind the goal was to try and make this a fair fight, so thats why there is some weird stuff in there (more so on the mysql side). http://pastebin.com/BCn3KdSc Edit: By adding this line SQLite was speed up dramatically (Credits to cessil for the line): Code: db_query(g_Sqlite, "PRAGMA synchronous = OFF" ); Here are the new results (MySQL is still not threaded in this example): Code: //test 1 [07:15:29] SQLite test starting... [07:15:30] SQLite Insert: 100 queries in 519 ms [07:15:30] SQLite Select: 100 queries in 12 ms [07:15:30] >> SQLite Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:30] SQLite Update: 100 queries in 342 ms [07:15:30] MySQL test starting.... [07:15:30] MySQL Insert: 100 queries in 23 ms [07:15:30] MySQL Select: 100 queries in 34 ms [07:15:30] >> MySQL Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:31] MySQL Insert: 100 queries in 120 ms //test 2 [07:15:50] SQLite test starting... [07:15:50] SQLite Insert: 100 queries in 439 ms [07:15:50] SQLite Select: 100 queries in 11 ms [07:15:50] >> SQLite Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:50] SQLite Update: 100 queries in 343 ms [07:15:50] MySQL test starting.... [07:15:50] MySQL Insert: 100 queries in 24 ms [07:15:51] MySQL Select: 100 queries in 34 ms [07:15:51] >> MySQL Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:51] MySQL Insert: 100 queries in 119 ms

[Calgon]

Strange, I swear I remember reading that SQLite was faster, that doesn't seem like a biased test though.

[Ryan_Obeles]

yeah i think mysql would be good but it would take me 3-4 weeks to set it up

[CoaPsyFactor]

If you want to make server with UCP, or anything that has Web use of player stats I recommend you MySQL, it is much faster and safer than SQLLite, and has much more features, also on php don't use mysql but mysqli

[CoaPsyFactor]

I think its enough to say, SQL Lite uses fopen fwrite, and MySQL has own system that doesn't depend on operating system, it is also not good for large databases (much slower), and here is one quote "not suitable where user management is needed as SQLite uses the file systems permissions so there is no way you will be able to use SQL statements such as GRANT and REVOKE.", and sql lite is slower if you don't use MyISAM engine for mysql.

[Pawnie]

For me the SQLite is easier. Simply because it's easier to set up and such. But if you want to have complex, secure system use MySQL. MySQL is harder to use but it's better.

[CoaPsyFactor]

But I forgot to say SQL Lite is cool cus you can simply copy/paste sql file (table)

[Finn]

Quote:

Originally Posted by Kyosaur Code: //test 1 [07:15:29] SQLite test starting... [07:15:30] SQLite Insert: 100 queries in 519 ms [07:15:30] SQLite Select: 100 queries in 12 ms [07:15:30] >> SQLite Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:30] SQLite Update: 100 queries in 342 ms [07:15:30] MySQL test starting.... [07:15:30] MySQL Insert: 100 queries in 23 ms [07:15:30] MySQL Select: 100 queries in 34 ms [07:15:30] >> MySQL Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:31] MySQL Insert: 100 queries in 120 ms //test 2 [07:15:50] SQLite test starting... [07:15:50] SQLite Insert: 100 queries in 439 ms [07:15:50] SQLite Select: 100 queries in 11 ms [07:15:50] >> SQLite Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:50] SQLite Update: 100 queries in 343 ms [07:15:50] MySQL test starting.... [07:15:50] MySQL Insert: 100 queries in 24 ms [07:15:51] MySQL Select: 100 queries in 34 ms [07:15:51] >> MySQL Last select result: STRRRRRRRRRRRRIIIIIIIIIIIIIIIIIIING TEEEEEEEEEST [07:15:51] MySQL Insert: 100 queries in 119 ms

I know 'plain' SQLite is slow like you stated there, but with proper usage you can reduce the time a lot.

I inserted an integer value and a unique player name (string), meaning I had to format it again and again for each row. I updated both values and selected them too (storing them into an array).

Here's my results, but I bet someone like Slice can make these even quicker:

QUERIES	INSERT	UPDATE	SELECT
100	6ms	7ms	4ms
100000	5657ms	4374ms	3883ms

I know this is old thread, but Kyosaur is bullshitting people and making SQLite seem slow on purpose - of course if you're planning to use SQL database you have to organize it properly and use it in the most efficient way possible. Benchmarking the slowest method and then coming here "LOL LOOK HOW SLOW THIS SHIT CAN BE" is useless.