How to build and optimize MySQL DB for multi-dimensional search?

I want to create a table with columns like: A1, A2, A3, L1, L2, L3, L4

The main job for this database is:

User provides some float number: a, b, c, d, then find the best one that have min Euclidean distance, that is the min of (a-L1)^2+(b-L2)^2+(c-L3)^2+(d-L4)^2

Also, some time user may provides some range information for A1, A2, A3,

e.g., A1 > 0.15, 2 < A2 < 3.5, A3 <= 1.2

and then based on these constraints, do the search for L1-L4.

I have read some topics related to this and done a test to insert all data into MySQL using MyISAM engine, and use command like:

select * from table1
order by (x-L1)*(x-L1)+ (y-L2)*(y-L2)+ (z-L3)*(z-L3) 
limit 1

But I want to improve the speed as fast as possible, I noticed that there are some optimization part. But still not clear how to do them, and which of them suitable for my case:

1. there are column index, but based on my problem, how to build index?
2. also there are "SPATIAL indexes", can I benefit from this? How to use this?
3. which search command should I use? stick on the "order" one that I'm using?
4. Anything else for improving the speed?
5. All the work are 开发者_如何学Pythondone in C/C++, I'm now using MySQL C API, and using mysql_query() function, is this the best way?

---

your result will be based on a specific formula.

As you are using Mysql 5 (i assume) can you try to create a procedure and after compiling

when ever you want you can call and performance will be better than the normal select query i guess.

you can pass the input parameters for that stored procedure as the range.

you can use indexes if you think the result set is based on any key.

but i dont really understand you have any key!!

The Mysql and C combination am hearing it for first time. i dont know how you will be seeing the result.(my less knowledge) :-(

---

In case you're still at the stage of experimenting with MySQL, you might also want to look into using Postgres.

It has a bunch of geometry types, for one.

And Postgres 9.1 in particular (in beta) implements out of the box k-nearest searches using the gist index (see E.1.3.5.5. Indexes). If the latter implementation doesn't fit your exact requirements, you'll also find it interesting that gist indexes are extensible.