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.
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?
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.
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.