mysql query connections and speed / alternative database solution necessary?

Question

I know you can execute 10 SQL queries inside a mysql_query() (or mysqli_...) but how is that different from executing 10 mysql_query()s with one SQL query in each one?

If they are different and the first solution was more efficient, how would I use mysql_fetch_assoc() function on one of the queries inside of it?

If the first solution allows me to limit the number of connections per page to 1 per mysql_query(), then I think I will have enough mysql connections to handle my traffic, but if it doesn't, what SQL technology (or other?) can I use that will allow me to connect to my database from PHP more efficiently (so I can handle more users)?

I am using Apache and PHP 5.4

Manse · Accepted Answer · 2012-07-11T15:32:55.873

2

You can only send a single query at a time ... see the docs

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

You could use mysqli_mutli_query .. example from the docs :

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();

edited Jul 11 '12 at 15:32

answered Jul 11 '12 at 15:27

Manse

37,765
10
83
108

you can by separating each query by sql.. http://stackoverflow.com/questions/345637/php-multiple-sql-queries-in-one-mysql-query-statement – Max Hudson Jul 11 '12 at 15:29
i could swear i have done it before with mutiple queries separated by semicolons, but if that's not possible, whats the difference between multi query and multiple queries? – Max Hudson Jul 11 '12 at 15:32
@maxhud without seeing the source I cannot confirm - but I suggest it would execute one after the other - storing the results in an array ... there are some pitfalls to watch out for on the `multi_query` - queries following an error wont execute for example - So i would suggest you have greater control over multiple queries – Manse Jul 11 '12 at 15:34

score 0 · Answer 2 · answered Jul 11 '12 at 15:36

The difference between running 10 queries in one call to mysql_query() and running 10 mysql_query() would be that in the first case you can only get the result from the very last query. But as long as you don't do a new mysql_connect() it shouldn't reconnect between queries.

The alternative is to use mysqli_multi_query which will let you run several queries in parallel which will optimize it some, but not a whole lot.

From the sound of it you don't need a more effective way to connect to your database, you either need to optimize your queries, sort indices and the like, or maybe the database machine is simply incorrectly configured or under-dimensioned hardware wise. I'm guessing your actual problem is that your database questions are too slow?

Just running with mysql_query(), one query per call, mysql running on a separate machine from PHP and you can still do hundreds of MySQL queries per second without breaking a sweat.

mysql query connections and speed / alternative database solution necessary?

2 Answers2