Hi there I've read a lot of questions about it but I haven't find the answer to my question. The question goes like this: I have a table in MYSQL with the following structure:
CREATE TABLE`Danni_Prekusvachi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`prekusvach_id` int(11) NOT NULL,
`data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`IzkOtZashtita` bit(1) NOT NULL,
`VklPrek` bit(1) NOT NULL,
`cVkl` bit(1) NOT NULL,
`cIzkl` bit(1) NOT NULL,
`greshki` text NOT NULL,
`komentari` varchar(255) NOT NULL,
`prava_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `prekusvach_id` (`prekusvach_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Danni_Prekusvachi` (`id`, `prekusvach_id`, `data`, `IzkOtZashtita`, `VklPrek`, `cVkl`, `cIzkl`, `greshki`, `komentari`) VALUES
(1, 1, '2013-07-25 13:07:24', b'1', b'0', b'0', b'1', '', ''),
(2, 1, '2013-07-25 13:07:25', b'1', b'0', b'0', b'1', '', '');
ALTER TABLE `Danni_Prekusvachi`
ADD CONSTRAINT `Danni_Prekusvachi_ibfk_1` FOREIGN KEY (`prekusvach_id`) REFERENCES `Prekusvachi` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;
I'm inserting data every second or so. I want to insert data only if one of the colons IzkOtZashtita, VklPrek, cVkl or cIzkl has changed its value. Right now I am thinking of checking with php whather the last inserted row is the same but my data base will have around 50000 rows so that will get slower as the database gets bigger.
Is there way to do the checking and inserting in one query or I have to call it two times to check the values?
EDIT: I want to insert only if value has changed compared to the last inserted row with the same prekusvach_id. So lets say if in one second time the value of cVkl has changed from 1 to 0 I should insert new row. But if the value of cVkl is still the same (1 to 1) the I should not insert new row
You can use triggers. There are triggers on update. Or you just use a php query which is sipmlear to be set up and check if the values have changes. Database of less then 1 milion record tend to be pretty fast but you should also do a re indexation from time to time so your queries are faster.
Composite unique constraint.
alter table Danni_Prekusvachi add unique index(IzkOtZashtita, VklPrek, cVkl, cIzkl);
EDIT: Than the previous one or than all the previous ones?
I'm pretty sure you should tackle this problem at a design stage. From the looks of it, you're trying to insert a new record each second, but only if something happend between those seconds.
Why not insert the record after this specific event (which changes your values) happend?
Abstract the check to the database layer. So write a procedure that checks if the values have changed and if so add a new row. One call for you and the database is doing the work rather than you.
Have a look at the REPLACE statement, it may be what you are looking for.
Assuming UID is a sequential running unique ID, you can check the value of a given field from the record where the UID field contains the maximum value and insert the new record only if the value of a given field ('id' in my example), is different than the 'id' field in the last created record.
INSERT INTO 'Danni_Prekusvachi' (`id`, `prekusvach_id`, `data`, `IzkOtZashtita`, `VklPrek`, `cVkl`, `cIzkl`, `greshki`, `komentari`) VALUES
(1, 1, '2013-07-25 13:07:24', b'1', b'0', b'0', b'1', '', ''),
(2, 1, '2013-07-25 13:07:25', b'1', b'0', b'0', b'1', '', '')
SELECT * FROM (SELECT * FROM 'Danni_Prekusvachi' WHERE UID = (SELECT MAX(UID) FROM TABLE)) AS tmp) WHERE NOT tmp.id = id
)