Best Optimisation Tips for Webservice and MySQL

I plan to build an iPhone client which would use web-service built on XML-RPC . I need to build a best system architecture to cater following needs:

1. It should be secure
2. Response Time Should be Fast and Quick
3. Performance

This is how I think my architecture would be:

1. iPhone client would invoke a web service via HTTPS (K开发者_C百科eeping security in MIND), I plan to bind a UNIQUE API KEY which would be passed to invoke any method provide by XML-RPC. The same key would be validated on the server side.
2. The Web service would in turn invoke MYSQL stored Procedures
3. Most of my business logic would go in the STORED procedure to improve performance as it would be compiled code
4. XML RPC Web Service would return response in XML format back to iPhone.

So my basic queries:

1. Is HTTPS and API Key enough from security point of view, what additional can be done to make it more secure?

2. For performance and optimization – are store procedures enough or we recommend to go with MYSQL mem-cahce – my system actually needs pull out records based on the geo-location, all nearest hotels and restaurants – So the Distance would always be different for each user and it won’t really be the same query – will mem-cache help?

3. What additional things can help to improve both Security, optimization and performance.
4. I also aim to index the database fields.
5. I missed to mention - The system is build on PHP + MySql

All suggestions would be appreciated, please do write your thoughts even if it would be small thing to do!

Thanks

For optimizing MYSQL, you might take a look at the optimization overview at dev.mysql.com. There are also more-detailed suggestions in the reference sections.

Tips for Mysql Query.

• you should know what is index in mysql and how to set index with proper column
• try to write join query with index column. this will give fast result
• avoid to use rand() function in query.
• avoid to use LIKE in query [first search alternative query method]
• if you are using DISTINCE try to use LIMIT with this in query
• don't use unnecessary () in query
• group by and having clause are heavy
• on place OR try to use UNION if possible

Importent

• Always use LIMIT 1 if you know result is single row. Because in table you have (example) 1000000 record your output track with in 10000 first record. if limit is 1 index stop the execution control will not search rest record for output and result will be fast.

• Use in-bult mysql function in query but Don't use in-bult function in where clause with index column.