Obviously they are not meant to be viewed, therefore searching on them would be problematic.
One trick I have used in the past is to hash the encrypted data before encrypting it, and storing the hash in an indexed column. Of course, this only works if you are searching on the whole value; partial values will not have the same hash.
You could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast.
ADDENDUM
It's been suggested that I add a footnote to my answer per a fairly lengthy debate in chat about vulnerability to dictionary attacks, so I will discuss this potential security risk to the above approach.
Dictionary Attack: A dictionary attack is when someone pre-hashes a list of known values, and compares the hashes to your hashed column in the database. If they can find a match, it's likely that the known value is actually what is being hashed (It's not definite though, because hashes are not guaranteed to be unique). This is usually mitigated by hashing the value with a random "salt" appended or prepended so the hash will not match the dictionary, but the above answer cannot use a salt because you lose the searchability.
This attack is dangerous when dealing with things like passwords: if you create a dictionary of popular password hashes, you can then quickly search the table for that hash value and identify a user that has such a password and effectively extract credentials to steal that user's identity.
It is less dangerous for items with a high degree of cardinality, like SSN's, credit card numbers, GUIDs, etc. (but there are different risks [read: legal] associated with storing these, so I am not inclined to advise on storing them).
The reason for this is in order for a dictionary attack to work, you need to have pre-built a dictionary of possible values and their hashes. You could, in theory, build a dictionary of all possible SSNs (a billion rows, assuming all formatting permutations are removed; multiple dozens of trillions of entries for credit cards)... but that's not usually the point of a dictionary attack, and basically becomes comparable to a brute-force attack where you are systematically investigating every value.
You could also look for a specific SSN or credit card number, if you're trying to match a SSN to a person. Again, usually not the point of a dictionary attack, but possible to do, so if this is a risk you need to avoid, my answer is not a good solution for you.
So there you have it. As with all encrypted data, it's usually encrypted for a reason, so be aware of your data and what you are trying to protect it from.
The solution to your problem is a MySQL capability called "partitioning". The documentation is here.
What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime
-- assuming that it is known when a record is created and it doesn't change.
You would store a day's worth of endTime
in each partition. Then the deletion step would be truncating a partition rather than deleting a bunch of rows in a big table. The partition truncation would be a much faster method.
Best Answer
I do something similar on my side, and I use a MySQL Stored Procedure / Cursor.
You loop through each line of the table and record the current datetime entry and plant_status into variables, and then move on to the next line.
At which point you compare the current datetime and plant_status with the last ones.
If it matches a set condition (in your case
new_status
!=last_status
) you add an entry to a temporary table.And then at the end you simply select * from temporary_table_name to show you all the rows where the status changed.
You could easily convert this to check the datetime as well (changing the old_datetime variable each time the status changes), and comparing that to the current one to find out if there is a two hour difference). (this probably should be a comment rather than an answer but I don't yet have enough points to comment - sorry)