dilemma about mysql. using condition to limit load on a dbf

Question

hi, I have a table of about 800 000 records. Its basically a log which I query often. I gave condition to query only queries that were entered last month in attempt to reduce the load on a database.

My thinking is a) if the database goes only through the first month and then returns entries, its good. b) if the database goes through the whole database + checking the condition against every single record, it's actually worse than no condition.

What is your opinion? How would you go about reducing load on a dbf?

Answer 1

If the field containing the entry date is keyed/indexed, and is used by the DB software to optimize the query, that should reduce the set of rows examined to the rows matching that date range.

That said, it's a commonly understood that you are better off optimizing queries, indexes, database server settings and hardware, in that order. Changing how you query your data can reduce the impact of a query a millionfold for a query that is badly formulated in the first place, depending on the dataset.

If there are no obvious areas for speedup in how the query itself is formulated (joins done correctly or no joins needed, or effective use of indexes), adding indexes to help your common queries would by a good next step.

Answer 2

If you want more information about how the database is going to execute your query; you can use the MySQL EXPLAIN command to find out. For example, that will tell you if it's able to use an index for the query.