We have the following two tables:
CREATE TABLE IF NOT EXISTS `gp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`amount` decimal(15,2) NOT NULL,
`user` varchar(100) NOT NULL DEFAULT '',
`status` tinyint(2) NOT NULL DEFAULT '1',
`ip` varchar(20) NOT NULL DEFAULT 'N/A',
`token` varchar(100) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `gp_logs` (
`id` int(11) NOT NULL,
`log` varchar(1000) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
We JOIN them, for statistics, but we do this rarely, since the data from the 2nd table is not used too often except when we need to verify things.
Considering that we have many queries per second, how can our query be optimized to use 1 INSERT query instead of two and to insert the correct id
in the 2nd table (gp_logs
) that was generated by the INSERT into table gp
?
Right now, we do a combination of MYSQL with PHP:
mysqli_query($con,"INSERT INTO `gp` (amount,user) VALUES ('1234','1')");
$id = mysqli_insert_id($con);
mysqli_query($con,"INSERT INTO gp_logs(id,log) VALUES ('$id','some_data')");
We want to eliminate the requirement of PHP for getting the last inserted ID and to insert both entries by running a single INSERT query (with a JOIN).