Accelerating query replies

krontrex · #1 (**permalink**) 01-09-12, 06:03

Dear all,
I need to reduce response time for query replies.
A table without foreign keys has been queried.
Queries are simple in the form:

Code:

SELECT col1, col3,col5 FROM mytable WHERE col1='something' AND col2="somethingelse" AND col4>123 AND col9<56 AND col15='some'

First thing is to set indexes:
1) Should I set a separate index on each column appearing in the query or a compound index that includes all columns being queries. In the latter case is the order of appearance of the columns in the index important?
What would be additional measures to accelerate response time?
Thanks

shammat · #2 (**permalink**) 01-09-12, 06:37

Quote:

Originally Posted by krontrex

col2="somethingelse"

That compares the value of column col2 against the value of the column somethingelse
Is that really what you want?
Ddouble quotes denote column names, single quotes denote character literals.

Quote:

In the latter case is the order of appearance of the columns in the index important?

Yes, absolutely.
You might want to go through: http://use-the-index-luke.com/ it has a lot of very good information on how to use indexes in a relational database

Quote:

What would be additional measures to accelerate response time?

EXPLAIN ANALYZE will tell you all you need to know

Internet Services

Web Development

Digital Marketing

Consumer Tech