Optimizing MySQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Paul Oertel
Forum Newbie
Posts: 18
Joined: Fri May 31, 2002 3:44 am
Location: Japan

Optimizing MySQL Query

Post 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
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post 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.
Post Reply