0

There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to this table is extremely slow. I need to know which configuration should I use to make the select query faster, that doesn't affects the speed of insertion.

Improve this question

1 Answer 1

Reset to default
0

Your problem is not the DB Engine, but the lack of the indices. Indices make possible for your database to find things (records) much faster. You had to make some.

I suggest to read an sql indexing tutorial (google is your friend), and then come back with a new question, if you have yet one.

Extension after you gave your query: in this case there is two problem. First, LIMIT isn't really simple to make faster with indices. But it is not a big problem, because in your query is it visible, what is the cause of the sloooow query. It is the multiple inequality conditions in the WHERE. I didn't give a medal for the liferay programmers for this sh... sometimes-maybe-not-really-useful solution.

The greatest problem is, that such indices can't be make faster by indices easily. On big tables they should be avoided, if we want to develop high-quality software.

The solution - indexing a table for multiple inequality search term - isn't known currently also for me, thus I opened a new question for you, what can be done in MySQL in your problem. Maybe we get some interesting answer and in this case I will be able to extend also this question.

Improve this answer
3
  • my query is "select * from school_liferay.trip_parameters where speed>5 and lattitude<100 limit 10;" it takes 265.9541s . Now i created an index with column speed and latitude but it is also taking the same time to query data.
    – Joyal George
    Commented May 27, 2014 at 13:15
  • In general > and < are not so fast as = or <> because such selects produce large or even huge temporary tables that sometimes can't fit into memory and placed on disk by mysql engine. That's the main cause of poor performance.
    – Kondybas
    Commented May 27, 2014 at 22:09
  • @Kondybas There is an alternative problem as well. If there is two non-equality term in a search condition, it can't be indexed with simple record-trees.
    – peterh
    Commented May 28, 2014 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.