MySQL query optimization---your thoughts

I know this is a very broad question but h开发者_如何学编程ere is my situation: 1) I've a hibernate enabled java program which dumps logs into MySQL (innoDB) every 15 minutes. The log data it self is humungous (100k-500k lines of log).

2) At present there are two things are being offered in frontend, searching and reporting based on the time stamps.

3) The query takes a looong time (10-15 mins) to get results back even though i've put indexes and did EXPLAIN to study where its taking time etc.

4) At the MySQL server level, I've increased query cache among other things.

I'm down to my knees to solve this problem. I need to know is there anything i can do to increase the query speed.

Thanks in advance. Bo

Use a fulltext search engine in conjunction with mysql.

For example sphinx. After every insert cycle let sphinx update it's cache. Than you can use sphinxse (use sphinx as a storage engine in mysql) to get your result. http://www.howtoforge.com/sphinx-as-mysql-storage-engine-sphinxse

Also 10 to 15 minutes seems to much time for 500000 inserts. You should use MyISAM as a storage engine for log tables and than use INSERT DELAYED instead of INSERT (I don't know how to achive that with hybernate).