MYSQL Inserting rows that reference main rows.

Posted by Andrew M on Stack Overflow See other posts from Stack Overflow or by Andrew M
Published on 2010-05-20T03:44:50Z Indexed on 2010/05/20 3:50 UTC
Read the original article Hit count: 224

Filed under:
|

I'm transferring my access logs into a database.

I've got two tables:

urlRequests
id : int(10)
host : varchar(100)
path: varchar(300)
unique index (host, path)

urlAccesses
id : int(10)
request : int(10) <-- reference to urlRequests row
ip : int(4)
query : varchar(300)
time : timestamp

I need to insert a row into urlAccesses for every page load, but first a row in urlRequests has to exist with the requested host and path so that urlAccesses's row can reference it.

I know I can do it this way:

A. check if a row exists in urlRequests B. insert a row in urlRequests if it needs it C. insert a row into urlAccesses with the urlRequests's row id referenced

That's three queries for every page load if the urlRequests row doesn't exist. I'm very new to MySQL, so I'm guessing that there's a way to go about this that would be faster and use less queries.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query