mysql fetch assoc VS mysql fetch array

It depends on how your tables are setup:

mysql_fetch_array() essentially returns two arrays one with numeric index, one with associative string index.

So using mysql_fetch_array() without specifying MYSQL_ASSOC or MYSQL_NUM, or by specifying MYSQL_BOTH will return two arrays (basically what mysql_fetch_assoc() and mysql_fetch_row() would return) so mysql_fetch_assoc() is faster.

If you have your table setup right and query written properly mysql_fetch_assoc() is the way to go, code readability wise $result['username'] is easier to understand than $result[0].

This Is the result of mysql_fetch_array()

$row['fieldname'] = 'some value';

or

$row[0] = some value';

This Is the result of mysql_fetch_assoc() will only return

$row['fieldname'] = 'some value';

I prefer fetch_assoc. It returns an "associative array" (like a python dictionary). This means your array is indexed by string (in my usual case).

The default for fetch_array gives both numbered indices and associative indices.

But I never use the numbered indices, so I like to get it a tiny bit faster with fetch_assoc

The benchark shown in your example uses mysql_fetch_array() without any argument to specify MYSQL_ASSOC or MYSQL_NUM, so it defaults to MYSQL_BOTH... this will rather skew the result as it has to load twice as many array elements as mysql_fetch_assoc(). So I'd suggest it isn't a valid benchmark.

In reality, there should be very little to differentiate between mysql_fetch_array() with MYSQL_ASSOC and mysql_fetch_assoc(), and it cerayinly won't be the biggest overhead in your script.

The "size" of the query results don't matter in this case.

mysql_fetch_array() generally produces overhead by returning an associative array plus indexed results.

One should decide on the type of query and the desired results on whether to use mysql_fetch_array() or mysql_fetch_assoc().

If the overhead is "neglectable" and I'm sure the query succeeds and I know there's only a single result, I occasionally do:

$q = mysql_query('SELECT `column1`,`column2` FROM `table` WHERE `id` = 123');
list($column1,$column2) = mysql_fetch_array($q);
mysql_free_result($q);

For iterative proceedings (like a while loop), this just doesn't cut it. When there's no need for "quick-extracting" results (via a list operator) and the result has to be further processed in code, I always use mysql_fetch_assoc()*.

* When forced to actually use the quite out-dated procedural data retrieval functions of PHP. There are alternatives.

mysql_fetch_assoc() would probably be the better choice. There is a slight performance hit (the mysql extension needs to look up the column names, but that should only happen once, no matter how many rows you have), but probably not enough to justify using mysql_fetch_array() instead. mysql_fetch_array() can be useful if you're only selecting one column though.

I suppose it depends on your definition of big. From that benchmark you can see that it is only before you perform upwards of 1,000,000 million fetches that you actually get a measurable difference? Is your database that big? If not, then go with what is easier for you to use (which is probably to fetch an associative array).

Another point to consider, if it is so big that there is a measurable difference then I would be getting a way from using functions like that all together. Consider using MySQLi or even better use PDO!

Well http://php.net/manual/en/function.mysql-fetch-assoc.php states

Note: Performance

An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.

So the difference shouldn't be something to worry about.

An additional point to keep in mind is that fetch_assoc() may not return every column you expect. If 2 output columns would have the same name, only the last instance of that column will be retrieved by fetch_assoc(). For example, the following query:

SELECT * FROM orders LEFT JOIN line_items ON orders.id = line_items.order_id

Assuming both tables have a column called id, the resulting associative array would have a key called id whose value is set to line_items.id and you would not be able to retrieve orders.id from the result!

You can circumvent this duplicate column names issue and continue to use fetch_assoc() by manually aliasing your SELECT columns, giving each column a unique alias:

SELECT 
o.id AS order_id, #unique alias
i.id AS line_item_id, #unique alias
o.date,
i.qty,
i.price
FROM orders o 
LEFT JOIN line_items i ON i.order_id = o.id

Or you can switch to using fetch_array(), which will allow you to access either id by index instead of by key

mysql_fetch_array() vs mysql_fetch_assoc()

mysql_fetch_array(): Returns an array that corresponds to the fetched row and moves the internal data pointer ahead. for better documentation click here! to learn more mysql_fetch_assoc(): is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.