Is this a Efficient way to query relational tables on MySQL?

Question

I'm dealing with a relational table and I've been wondering if there's a way to lower the number of queries I need to make when inserting data to the tables..

Here are the queries I currently use:

I insert the "main" values.

INSERT INTO products
        (title, description, status, url)
    VALUES
        ('some title', 'description of doom', 1, 'some-title');

We make it insert the value only if it doesn't exist already.

    INSERT IGNORE INTO values
            (value)
        VALUES
            ('example value');

Since I'm not sure if the query was actually inserted, I get the id..

            SELECT id
                FROM
                    values
                WHERE
                    value = 'example value';

Where "?" is the ID I got from the last query.

                INSERT INTO link
                        ( id_product, id_catalog, id_value )
                    VALUES
                        ( 33, 1, ? );

This means that each extra value I need to add will cost 3 queries. So my question is: Is there a more efficient way to do this?

Answer 1

"Is there a more efficient way to do this?"

No. Not really. Creating three things takes three inserts.

Answer 2

You can do this to at least drop one of the queries:

INSERT INTO link
     ( id_product, id_catalog, id_value )
VALUES
     ( 33, 1, (SELECT id
                 FROM values
                WHERE value = 'example value') );

I basically am replacing the '?' with a sub select of the second query to get the id.

Answer 3

You should be able to tell whether the insert succeeded with the ROW___COUNT() function from inside MySQL. If calling from another language (e.g. PHP), the mysql_query or equivalent function will return the row count.

Answer 4

You could use an INSERT INTO ... ON DUPLICATE KEY UPDATE statement.

This does, however, require that the primary key be one of the values for the insert, so it doesn't work on tables with an auto-increment.