mysql or sqlite

[Cool]

hello i want to ask which one is better sqlite or mysql and is it possible to move sqlite data to mysql

[DizzasTeR]

2. Is mySQL faster than sqlite?

MySQL is way faster inserting records, but loses that advantage when selecting a lot of rows one after another.
SQLite does it completely the other way around. Slow with inserting (needs a lot of IO operations), but faster with selecting.
I think this is to do with the memory loading SQLite does. It keeps parts of it database in memory, so selecting rows from there is pretty quick. Below is the benchmark results done by Thijin.

[SCRIPT]  MySQL Benchmark
[SCRIPT]  Start: 0.091
[SCRIPT]  Connection made 0.098
[SCRIPT]  Table made 0.121
[SCRIPT]  10.000 records inserted: 3.569
[SCRIPT]  10.000 records selected: 55.287
[SCRIPT]  MySQL finished.

[SCRIPT]  SQLite Benchmark
[SCRIPT]  Start: 0.093
[SCRIPT]  Connection made: 0.094
[SCRIPT]  Table made: 0.1
[SCRIPT]  10.000 records inserted: 44.053
[SCRIPT]  10.000 records selected: 48.065
[SCRIPT]  SQLite finished.

There's your first answer, speaking of transferring data from sqlite to mysql, yes its possible

[KAKAN]

Short answer: It depends.
For ex: If you're going to release your script for everyone, then SQLite might be a good choice since it is portable, and doesn't need to setup can servers.
But, if you're going to do a lot of work, i.e, real SQL work( in VCMP, it's not needed ) then you might prefer MySQL over SQLite since it has many extra features and pretty fast too.

[jWeb]

@Doom_Kill3R That benchmark is a bit misleading because it's limited by the official SQLite plugin. Had you been using transactions, the situation would've been entirely different. Not to mention that the test itself is flawed because it does not decrease the time from previous operations from the time of current operation. Therefore, the current operation time also includes the sum of all previous operations.

Here's the SQLite test translated to a plugin that takes advantage of the features that SQLite has to offer:

Code Select Expand


function sqlite()
{
    local t = SqTimer();

    print( "SQLite Benchmark" );
    printf( "Start: %dms", t.RestartRaw() / 1000);

    local db = SQLite.Connection("test.sqlite");
    printf( "Connection made: %dms", t.RestartRaw() / 1000 );

    db.Exec(@"CREATE TABLE IF NOT EXISTS `benchmark` (`testInt` INTEGER, `testChar` TEXT, `testFloat` REAL);");
    printf( "Table made: %dms", t.RestartRaw() / 1000 );

    {
        // Pre-compile the query
        local query = db.Query("INSERT INTO `benchmark` (testInt, testChar, testFloat) VALUES(@testInt, 'test string omfg', 123456.789);");
        // Cache the parameters
        local p_testInt = query.Param("@testInt");
        // Begin transaction
        local transaction = SQLite.Transaction(db);

        for( local i = 0; i < 10000; i++ )
        {
            query.Reset(); // Reset the query
            p_testInt.SetInteger(i); // Update parameter value
            query.Exec(); // Execute query
        }

        // Commit changes
        transaction.Commit();

        printf( "10.000 records inserted: %dms", t.RestartRaw() / 1000 );
    }

    {
        // Pre-compile the query
        local query = db.Query("SELECT * FROM `benchmark` WHERE `testInt` = @testInt;");
        // Cache the parameters
        local p_testInt = query.Param("@testInt");

        for( local i = 0; i < 10000; i++ )
        {
            query.Reset(); // Reset the query
            p_testInt.SetInteger(i); // Update parameter value
            query.Step(); // Step query
        }

        printf( "10.000 records selected: %dms", t.RestartRaw() / 1000 );
    }

    print( "SQLite finished." );
}

sqlite();

Output (time is in milliseconds):

Code Select Expand

[USR] SQLite Benchmark
[USR] Start: 1ms
[USR] Connection made: 1ms
[USR] Table made: 79ms
[USR] 10.000 records inserted: 150ms
[USR] 10.000 records selected: 4353ms
[USR] SQLite finished.
The kind of impact that transactions bring to the table when you insert and also using pre-compiled queries and cached parameters to the overall performance.

Although, I'll have to also run the MySQL benchmark so you can have a point of reference. But still, the difference is quite dramatic, don't you think?

And there's still a lot more to optimize in that benchmark.

[DizzasTeR]

@jWeb, The fact that he's using it for VCMP, so in context to that my reply is perfectly fine. If you're telling me to go for the full glory of SQLite, then there's alot of things to raise fingers upon but since right now you're sandboxed to VCMP only so basically staying inside the VCMP box is enough to respond on such situations.

Nonetheless, I personally prefer MySQL over SQLite due to various reasons. Taking SQLite as better just because its so easily portable then I suppose one should take his lazy a$$ up and just spend a few minutes to make a MySQL setup.

[Thijn]

It's funny you use transactions, but in VC:MP you'd probably never use those since you're not going to insert 10k rows of data each time. It's probably 2 or 3.

I personally like MySQL more because you can remotely access it, which can be very useful to administrate it while your server is running.
With SQLite you'd have to shut down the server, download the database, edit it, reupload and start your server again in order to change something.

[Cool]

i want to move from sqlite to mysql can any one guide me how to transfer data from sqlite to mysql

[KAKAN]

I personally like MySQL more because you can remotely access it

Same reason
And, for you all( most people ), this won't matter at all. You're not doing anything critical.

i want to move from sqlite to mysql can any one guide me how to transfer data from sqlite to mysql

export and import is the easiest way.
http://www.databasejournal.com/features/mysql/importing-into-mysql-from-other-databases.html

[jWeb]

@Thijn My point was that the results of initial posted benchmark did not reflect the true performance that can be obtained through SQLite. I'm not saying that SQLite is better or worse. What I'm saying is that in the case where performance is your true goal, that you should strive to actually use an efficient implementation. Instead of writing some plain code and expecting it to run fast.