It sounds like Indexing is probably the issue. As the second usage of a record is fast, it implies that the database system has cached a reference to it.

Indexes allow the database system to do the initial finding of records more quickly, by storing some meta-data about the data (much like the index you at the back of a reference book). Depending on your data and the indexes you use, the speed increase can be immense.

It isn't all good news tho, as they they increase the database size and memory usage a little (ignorable in your case), and make adding/editing/deleting values in the relevant columns slower (my educated guess is that you will benefit overall, especially if you don't often edit the X/Y values or add/delete records).

Don't index everything, just the fields that are needed most often in Where clauses (I presume just X and Y).

As for how you set them up, I can't help with the details as I don't use MySQL.


One thing to think about is whether the X and Y values have a finite range (eg: will X always be between 0 and 999?), because if so you could combine the X and Y values into one field (eg: X+(Y*1000) ), which would enable the indexes to be even faster and the data/memory usage to be smaller.