Page 1 of 1

Optimizing MySQL Query

Posted: Wed Jul 10, 2002 2:45 am
by Paul Oertel
I have a batch script that runs a series of queries and creates a report. As the database grows the batch file is taking longer and longer to complete. My queries are very simple but I'm wondering if there is some way to optimize them. The table contains Windows NT security log data. It grows larger every night as new data is imported.

Code: Select all

select eventid, eventtime, userid, description from logdb.sec where eventid="529" and computer="server1" and (to_DAYS(current_date)-to_DAYS(eventdate))=1;
The result of an explain query of the above query is as follows.

table = sec
type = ref
possible_keys = eventid,computer,eventid_eventdate
key = computer
key_len = 16
ref = const
rows = 1
Extra = where used

Can anyone tell me how to get more mileage out of this? Is there a way to force MySQL to use a specific index?

Paul

Posted: Wed Jul 10, 2002 10:25 am
by llimllib
When you frequently issue a query, what you want to do is make sure that mySQL indexes it. Read the manual about indexing here

Posted: Thu Jul 11, 2002 3:41 am
by mikeq
What are you trying to acheive with the date stuff in the where clause, reason for asking is that (this is related to Oracle so I'm not sure if the same behaviour exists in mysql) if you modify a column in the where clause i.e. to_DAYS(eventdate) then the query cannot use any indexes defined for that field.

So you may want to look at that date thing.

Mike

Posted: Thu Jul 11, 2002 8:28 am
by llimllib
I just reread the docs, and it doesn't mention the behavior you're talking about. Some of the example queries use max() and min() in them. However, it doesn't specifically mention the use of functions in the query, so you may be right. Either way, I think it's worth a shot to try and index the query.