Does fetching a mysql array with MYSQL_ASSOC rather than MYSQL_BOTH affect performance

Simple question I can't seem to find the answer to.

I'm trying to squeeze some more performance out of this large app and I'm wondering if switching all of the mysql_fetch_array() function calls to only use MYSQL_ASSOC will make any difference. It seems like it would since it's creating an array with just:

$row['field1'] = 'field1-value';

instead of both numeric 开发者_StackOverflow中文版and associative keys for the same field:

$row[0] = 'field1-value';
$row['field1'] = 'field1-value';

There are two concepts prevalent in this scenario, which you must consider:-

1. It is always helpful to use the associative indices of "MYSQL_ASSOC" / "MYSQL_BOTH", instead of just using the numerical indices, because the order of the fields may change in the lifetime of the website. If you understand & agree with this point, then you can use the "MYSQL_ASSOC" result type, instead of the "MYSQL_BOTH" result type.

2. If you are concerned about how much memory is being used for queries that return large result sets, then you can use "mysql_free_result()" function. More details of this function can be found here. However, all the associated result memory is automatically freed at the end of the script's execution.

Now, coming back to your question, I think that from a performance point of view, using "MYSQL_ASSOC" result type may result in saving a little bit of memory of the server (where the website has been hosted, when the relevant web page is being executed by the PHP Parser), but you must also ensure & understand the above points as well.

Hope it helps.

I had the exact same question. To get a better idea, I ran a little experiment:

I ran the same query three times (with MYSQL_ASSOC, MYSQL_BOTH and MYSQL_NUM respectively) for several times to get three average timings. Turned out that in my experiment MYSQL_NUM was about 17% faster than MYSQL_ASSOC, and MYSQL_ASSOC was about 16% faster than MYSQL_BOTH.

I'm sure my little experiment has its flaws, but I'm confident it goes into the right direction.

So I'd say MYSQL_ASSOC gives you a good compromise because you can still switch columns in your table...

$sql = "SELECT candidate.cand_number, candidate.cand_fname, candidate.cand_desc 
        FROM candidate ".$join.' 
        WHERE '.$condition;
$result = mysql_query($sql) or die(mysql_error());