Mysql single query with subquery or two separate queries

Question

I've table with following structure :

id | storeid | code

Where id is primary key

I want to insert data in this table with incremental order like this :

id | storeid | code
1  |       2 | 1 
2  |       2 | 2 
3  |       2 | 3 
4  |       2 | 4

I've two solution to do this task.

1) Fire a query to get last record (code) from table and increment value of code with 1 using PHP and after that second query to insert that incremented value in database.

2) This single query : "INSERT INTO qrcodesforstore (storeid,code) VALUES (2,IFNULL((SELECT t.code+1 FROM (select code from qrcodesforstore order by id desc limit 1) t),1))"

I just want suggestion which approach is best and why for performance. I'm currently using second method but confuse about performance as I'm using three level sub query.

I think you can set `AUTO_INCREMENT` to a column when creating a table. Also you need to make an index for this column — akasummer, Feb 19 '16 at 11:57
`Id` is already set to `AUTO_INCREMENT` . I'm talking about field `code`. — Mr. Engineer, Feb 19 '16 at 12:00
As i already said i've `id` field and i dont want two `AUTO_INCREMENT` field in my db. — Mr. Engineer, Feb 19 '16 at 12:06
I would use a single query, as to queries means 2 trips to the database which is a large over head (and the chance of another insert happening at that moment so you get duplicates). But I wouldn't use the ordered sub query you are using. Another option would be a trigger to add the value on insert. — Kickstart, Feb 19 '16 at 12:37
@Kickstart Completely agree with this : `The chance of another insert happening at that moment so you get duplicates`. I never thought about that. And can you please explain me how this can be achieve with trigger in your answer. Thanks. — Mr. Engineer, Feb 19 '16 at 12:43

mitkosoft · Answer 1 · 2016-02-19T12:30:01.660

1

You simply can use INSERT with SELECT and MAX():

INSERT INTO qrcodesforstore 
(storeid, code) 
(SELECT 2, IFNULL((MAX(code)+1),1) FROM qrcodesforstore)

SQLFiddle

edited Feb 19 '16 at 12:30

answered Feb 19 '16 at 12:04

mitkosoft

5,262
1
13
31

score 0 · Answer 2 · answered Feb 19 '16 at 11:59

0

you declare the field as primary key and unique and auto increment key they automatically increment the values

answered Feb 19 '16 at 11:59

Karthick Kumar

77
1
12

I don't want two `AUTO_INCREMENT` field in my db. Thanks – Mr. Engineer Feb 19 '16 at 12:11
simply after inserting of last data get last_insert id to update that record. – Karthick Kumar Feb 19 '16 at 12:19
Thanks again for comment. Please read my question carefully what i want to do. – Mr. Engineer Feb 19 '16 at 12:21

score 0 · Answer 3 · edited May 23 '17 at 12:23

0

You can set the code column as AUTO_INCREMENT, you don't need to set it as primary key, see this.

Anyway, the second solution would be better, only one query is better than two.

edited May 23 '17 at 12:23

Community

1
1

answered Feb 19 '16 at 12:00

drosam

2,866
3
16
18

I know one query is better than two. That's why I'm using it. But what about sub queries. Is it OK? – Mr. Engineer Feb 19 '16 at 12:13

score 0 · Accepted Answer · answered Feb 19 '16 at 13:08

0

Wrapping it up in a trigger:-

DELIMITER $$
DROP TRIGGER IF EXISTS bi_qrcodesforstore$$
CREATE TRIGGER bi_qrcodesforstore BEFORE INSERT ON qrcodesforstore
FOR EACH ROW
BEGIN
    DECLARE max_code    INT;
    SELECT MAX(code) INTO max_code FROM qrcodesforstore;
    IF max_code IS NULL THEN
        SET max_code := 1;
    ELSE
        SET max_code := max_code + 1;
    END IF
    SET NEW.code := max_code;
END

answered Feb 19 '16 at 13:08

Kickstart

21,403
2
21
33

But how this trigger will fire. For that i need to insert some data in database with query. – Mr. Engineer Feb 19 '16 at 13:11
Yes, if you insert a row the trigger is automatically fired off and will give the next value of code for the insert – Kickstart Feb 19 '16 at 13:12
So on first insert i add blank data into `code` field and this trigger will update `code` field with `max+1`. That's what you are saying? – Mr. Engineer Feb 19 '16 at 13:16
You do not even need to add anything in the code field on the insert statement (and if you did it would be ignored). Just do _INSERT INTO qrcodesforstore (id, storeid) VALUES(NULL, 5535)_ – Kickstart Feb 19 '16 at 13:20

Mysql single query with subquery or two separate queries

4 Answers4