MySQL

[LasTRace]

hi there,

I've started this topic, because i wanted to know the difference between normal pawn script and MySQL. Nowadays everyone is changing their scripts to MySQL. So, its popular nowadays...

- I want to know, what MySQL gives the script as plus? Or, why the ppl change their scripts to MySQL?

Waiting for your answers.

LR

[Lithirm]

MySQL isn't a language to replace PAWN, it can be used with pawn. MySQL is a database to store information. So you can use that instead of let's say dini.

With MySQL you can use a webpage with PHP to display the information on the web as well. So you can create statistics pages etc.

[MidNight]

use y_ini its easy and the fastest way to write & save

[thiaZ]

use y_ini its easy and the fastest way to write & save

I bet it's not faster than MySQL when storing huge data and multiple fields. Also when using MySQL you can use the MySQL database to create websites too, PHP for example also supports MySQL, that allows you to create your own web-statistics like LVP has. When using MySQL you have a lot of more functions than using any INI system, it's also easier to manage user data when using phpMyAdmin.

[Kase]

Actually, MySQL is a replacement to the save-load data system, eg. users. MySQL is way faster than any other file manager, and it's used to manage websites too.

Y_ini could be good, but it's not going to be any faster than MySQL.

[iou]

Y_ini would be about as fast as dini, which isn't fast at all.
If you save an account with several fields, maybe 10, the server would lag for like 0,2 seconds.
Imagine that in a loop or on OnPlayerUpdate like several fools have actually done before. LAG ON!

What about MySQL? It would still be like 0,01 seconds but that's unnoticeable.
Unless you use above example of course, never do that!

[Matthias]

As I didn't believe iou his "0.2 seconds vs 0.01 second" comparision, I quickly wrote up a testing script that stores a string, an integer and a float, and removes them afterwards. The first 3 tests were done with looping 1000 times, the last 3 I looped 10.000 times. As I expected, y_ini and dini are actually almost equally fast with less data yet MySQL is faster with more data.

The results:

Looping 1000 times:

Code:

[13:20:43] It took 4061 milli seconds to save the data using Y_ini!
[13:20:47] It took 4207 milli seconds to save the data using MySQL!

[13:21:43] It took 4632 milli seconds to save the data using Y_ini!
[13:21:47] It took 4056 milli seconds to save the data using MySQL!

[13:22:46] It took 4350 milli seconds to save the data using Y_ini!
[13:21:47] It took 4056 milli seconds to save the data using MySQL!

Looping 10.000 times:

Code:

[13:26:02] It took 64192 milli seconds to save the data using Y_ini!
[13:26:42] It took 40249 milli seconds to save the data using MySQL!

[13:28:35] It took 47894 milli seconds to save the data using Y_ini!
[13:29:14] It took 39851 milli seconds to save the data using MySQL!

[13:32:15] It took 60405 milli seconds to save the data using Y_ini!
[13:33:03] It took 47818 milli seconds to save the data using MySQL!

The code I used to test this:

Code:

main()
{
    new iStart = GetTickCount();
    
    for(new i = 0; i < 10000; i++)
    {
        new INI:fIni = INI_Open("test.ini");
        INI_WriteString(fIni, "foo", "Matthias");
        INI_WriteInt(fIni, "bar", 42);
        INI_WriteFloat(fIni, "bla", 3.14);
        INI_Close(fIni);
        fIni = INI_Open("test.ini");
        INI_RemoveEntry(fIni, "foo");
        INI_RemoveEntry(fIni, "bar");
        INI_RemoveEntry(fIni, "bla");
        INI_Close(fIni);
    }
    
    new iEnd = GetTickCount();
    new iRunTime = (iEnd - iStart);
    printf("It took %d milli seconds to save the data using Y_ini!", iRunTime);
    
    iStart = GetTickCount();
    
    for(new i = 0; i < 10000; i++)
    {
        mysql_init();
        mysql_connect("localhost", "root", "password", "database");
        mysql_query("INSERT INTO samp (foo, bar, bla) VALUES ('Matthias', '42', '3.14')");
        mysql_query("DELETE FROM samp WHERE foo = 'Matthias'");
        mysql_close();
    }

    iEnd = GetTickCount();
    iRunTime = (iEnd - iStart);
    printf("It took %d milli seconds to save the data using MySQL!", iRunTime);
}

[Slice]

Matthias, you should check out this topic I made about benchmarking: http://forum.sa-mp.com/showthread.php?t=218491
Also, MySQL can be even faster than your results as you didn't thread your queries. If you thread the queries then it should take only a few nanoseconds for PAWN to run a query.

use y_ini its easy and the fastest way to write & save

man.. you're just stupid.

Y_ini would be about as fast as dini, which isn't fast at all.

dini is way slower than y_ini. Loading 400000 values took y_ini 1564 ms, but just above a minute for dini.

f you save an account with several fields, maybe 10, the server would lag for like 0,2 seconds.

With y_ini, my guess is it wouldn't take more than 1-3 ms.

To sum this up (very briefly):
MySQL:

• Is significantly faster.
  
• Can be read from and written to simultaneously by multiple applications.
  
• Can perform things such as sorting, filtering, grouping to your data.
  
• Can be located externally.
  

y_ini:

• Is simple and fast enough for small servers.
  
• Allows for easy modifications to the data by simply editing text files.
  

[Russell]

Actually, MySQL is a replacement to the save-load data system, eg. users. MySQL is way faster than any other file manager, and it's used to manage websites too.

Y_ini could be good, but it's not going to be any faster than MySQL.

These are interesting results, I would not have expected that, and it may indicate that the computer these tests were run on may have a faulty harddrive. Storing MySQL results in a synchronous fashion requires at least two round-trips to the MySQL server, three if you're not using a persistent connection. A simple write to the file system is very likely to be faster. Are you sure that the query methods ran later on didn't run asynchronously? Still, SA-MP's built in SQLite should be even faster.