Today, I optimized a MySQL query to find spatial points by proximity. Initially, my query took an average of 250 ms.
After some research, I came across a document from MySQL. By following their recommendation to use a preliminary approximation via a bounding box, I was able to reduce the query time to a range of 5 to 35 ms.
However, as I continued reading, I discovered that MySQL also offers a spatial extension based on R-Tree. I thought I had found the perfect solution. But no, R-Trees are only available with the MyISAM engine, and creating spatial indexes is not allowed in InnoDB. The same goes for FULLTEXT indexes, which are also not available in InnoDB.
We are therefore faced with a choice between a less reliable database (MyISAM) and a robust one with fewer features (InnoDB). Of course, there are solutions, such as duplicating tables in both InnoDB and MyISAM to benefit from FULLTEXT indexes, but this quickly becomes cumbersome.
I want to put this criticism into perspective. Many people, especially in non-web companies, associate MySQL with its version 3 and consider it limited. However, MySQL with the InnoDB engine is very robust for managing critical data. Even after power outages during massive writes, I have never encountered data corruption. Its relational and transactional capabilities make it an excellent storage engine.
Currently, I have tables containing over 10 million rows, and InnoDB handles it without any issues. I will update you when it reaches 20 million.
Update from 15/05/2010:
The project eventually reached 35 million rows, and InnoDB showed no signs of weakness.