MySQL gurus needed! (Optimization)

MySQL gurus needed! (Optimization) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL gurus needed! (Optimization) (/showthread.php?tid=314956)

MySQL gurus needed! (Optimization) - -Prodigy- - 01.02.2012

I want to know if anyone knows an optimization for this MySQL code:

pawn Код:

// In a timer each 3 minutes
foreach(Player, i)
{
    format(gQuery, sizeof(gQuery), "SELECT * FROM `Tests` WHERE `IP` = '%s'", gPIP[i]);
    mysql_query(gQuery, THREAD_TEST, i);
}

This is the thread code:

pawn Код:

case THREAD_TEST:
{
    mysql_store_result();

    if(IsPlayerConnected(extraid))
    {
        mysql_retrieve_row();

        mysql_get_field("Test1", iResult);
        mysql_get_field("Test2", iResult);
        mysql_get_field("Test3", iResult);
        mysql_get_field("Test4", iResult);
        mysql_get_field("Test5", iResult);
        mysql_get_field("Test6", iResult);
        mysql_get_field("Test7", iResult);
    }
    mysql_free_result();
}

There's around 50 columns in that table. Is there a way to just select those 7 results without getting the entire row?

Also, if anyone knows of a way to optimize the first query check(Loop part), please let me know. I'm afraid this might lag after a few players come online(20ish or so).

Re: MySQL gurus needed! (Optimization) - cessil - 01.02.2012

I'm not an expert on this

pawn Код:

format(gQuery, sizeof(gQuery), "SELECT `Test1`, `Test2`, `Test3`, `Test4`, `Test5`, `Test6`, `Test7` FROM `Tests` WHERE `IP` = '%s'", gPIP[i]);

edit:
it'd be good to know why you need to run a query x times per 3 minutes, you could use variables depending on what you're doing

Re: MySQL gurus needed! (Optimization) - Vince - 01.02.2012

To select only specific rows, just replace the star (*) with the column names.

PHP код:



SELECT `test1`,`test2`,`test3` FROM `table`

The thread code itself seems pretty solid, although it may be more useful to use sscanf. Not sure if it's any faster though. I don't know how or if you can get rid of the loop, because I don't know what the underlying idea is.

Edit: Damn, I'm slow.

Re: MySQL gurus needed! (Optimization) - -Prodigy- - 01.02.2012

Quote:

Originally Posted by cessil

I'm not an expert on this

pawn Код:

format(gQuery, sizeof(gQuery), "SELECT `Test1`, `Test2`, `Test3`, `Test4`, `Test5`, `Test6`, `Test7` FROM `Tests` WHERE `IP` = '%s'", gPIP[i]);

Thanks for the replies.

When I run that query, on the thread check, I would still use the "mysql_store_result();" function or not? Or how would I get the data for those 7 results?
(Pretty confused on this

)

@ cessil's edit: I need to have it like this because data is being sent from an application that the user runs/closes. So if that user closes his application(it will then update his row in the db) and I need to constantly check if he still has the app running or not. This is just part of the script.

Re: MySQL gurus needed! (Optimization) - Dokins - 01.02.2012

Yes you still store it using: mysql_store_result();

But first use mysql_query(gQuery);

then use mysql_free_result();