CODEWITHSUNDEEP
phpmysql
Jorhel Reyes
Jorhel Reyes

Reputation: 142

Create new connection for each query?

Note: I used Google Translator to write this

I've always done the following to work with MySQL:

-> Open Connection to the database.
-> see details
-> Insert Data
-> another query
-> close Connection

I usually use the same connection to do various things before closing. A friend who studies this in the IPN of Mexico mentioned to me that the right way (for safety) is to make a new connection for each query, for example:

-> Open Connection to the database.
-> see details
-> close Connection
-> Open Connection to the database.
-> Insert Data
-> close Connection
-> Open Connection to the database.
-> another query
-> close Connection

My question is, what is the right thing to do? My method has been to make the least amount of queries to the database, and only make a connection and keep it until it no longer serves me.

Additionally, is it possible to make a double insertion to a table? For example:

insert into table1(relacion) values([insert into tablaRelacionada(id) values("dato")]);

and that "relacion" is the inserted ID from the first query in "tablaRelacionada".

Upvotes: 3

Views: 3124

Answers (2)

Sammitch
Sammitch

Reputation: 32232

Using a separate connection per query is at best a great way to bog down both your application and database servers with needless overhead. There are three aspects I see raised here:

  1. Efficiency
  2. Application Security
  3. Network Security

1. Efficiency

Short answer: Bad idea.

Oftentimes the overhead required to initialize the connection is far more than what is required to run the actual query. Your application is probably going to run orders of magnitude slower if you take a connection-per-query approach.

2. Application Security

Short answer: Generally a bad idea, but in the context of PHP completely unnecessary.

The only 'safety' issue I can think of here would be worrying about users accessing leftover temp tables, or session settings "bleeding" over. This is unlikely to happen unless you're using persistent connections which are not the default. As well, most temporary values in MySQL are stored per-connection, and unless you have some PHP code that written poorly [in a particular, strange, and seldom-recommended way, ie. sharing around DB singletons and accessing them strangely] then maybe if the planets align just right you might access some MySQL session-specific data in an unexpected way.

This is pretty much the same as preemptive optimization, and is not worth worrying about.

3. Network Security

Short answer: No. What? Just... no.

If you're worried about someone peeping in on your connections the solution is not to make more of them, it to make them securely. MySQL supports SSL, so use that if you're worried.

 

TL;DR No. Don't create separate connections per-query. Bad. Whoever told you this needs to go back to school.

Multi-Table Insert

What you've quoted is not possible, you would want to do something along the lines of the following:

$dbh->query("INSERT tablaRelacionada(id) values('dato')");
$lastid = $dbh->lastInsertId();
$dbh->query("INSERT INTO table1(relacion) values($lastid);");

Assuming that the table tablaRelacionada has an AUTO_INCREMENT column which is what you're trying to get from the first query.

See: lastInsertId()

Upvotes: 2

spencer7593
spencer7593

Reputation: 108380

No, it's not possible to insert rows into two different tables with a single INSERT statement. (You can use a trigger to get it done, but that trigger will need to issue a separate INSERT statement... from the client side it will look like one statement, but on the server, there would be two INSERT statements executed.

If performance and scalability aren't concerns, then "churning" connections is workable. There's nothing necessarily "wrong" with creating a separate connection for each statement, but it's resource intensive. There is a lot of overhead in creating a new session. (It looks rather simple from the client side, but it requires a lot of work on the server side, in addition to the codepath on the client.)

Reusing existing connections is a common pattern. It's one of the biggest benefits of implementing "connection pool", to make it easy to reuse connections without "churning", repeatedly connecting and disconnecting from the database.

In terms of a separate connection for each SQL statement somehow increasing "safety", that's a bit of a stretch.

But I can see some benefit of having a freshly initialized session.

For example, if you reuse an existing session, you may not know what changes have been made in the session state. Any changes made previously are still "in effect". This would be things like session variable settings (e.g. timezone, characterset, autocommit, user defined variables) which could have an impact on the current statement. But within a single script, where you've gotten a fresh connection, you should know what changes have been made, so that shouldn't really be an issue. (This would be more of an issue with using connections from a pool, where the connections are shared by multiple processes. One process mucking with the timezone or characterset could cause a slew of problems for other processes that reuse the connection.)

Upvotes: 3

Related Questions