I'm working on a game and I relate a plate number of a car to a user who owns the car.
When I execute the first query to find the owner of a car, it takes approx. 1 second
SELECT aid FROM owned_vehicles INNER JOIN users ON users.identifier = owned_vehicles.owner WHERE vehicle LIKE '%04IBQ509%' LIMIT 1
This results in the aid, what relates to the primary key of the user who owns the car. In this case
aid
11
But when I use the same query on another plate number, it takes more than 30 seconds to load the data (found this problem when users were complaining about the load time and the timeout message)
SELECT aid FROM owned_vehicles INNER JOIN users ON users.identifier = owned_vehicles.owner WHERE vehicle LIKE '%85RAF179%' LIMIT 1
After more than 30 seconds this results in 5475. How can it be that this new car takes a lot of more time to load?
In trying to solve this problem I've excecuted this queries both in HeidiSQL (MySQL GUI) and that results also in strange executing times
I tried to create an index of the vehicle column, to speed it up. But that makes no diffrence.
Does someone know how to solve my problem and speed up my query? (Excecuting should take as max of 2 seconds)
You are performing a LIKE with the wildcard % at the beginning. This precludes the use of any index on the vehicle column. If you remove the % at the start of the vehicle number and then add an index to that column you will see hugely improved performance. Of course this changes the nature of the query but as things stand you are performing a table scan on the owned_vehicles table which is not going to be sustainable for a query that is executed with any regularity in your game.
Having seen the comments above it appears your vehicle column is actually JSON data. You need to rethink the way you are storing this. If you need to very quickly query for vehicles with the registration number you will need to extract that registration to another column, index it and filter via that column. Attempting to filter based on a LIKE of a JSON string is not what an RDBMS is for. Apart from anything else you cannot be sure that the strings you are passing in will not match with another property (other than the registration) in the JSON string.