Bruce is correct. If you need to do a query based on a time frame, try something like
timestamp >= NOW() - interval X seconds; // where X is your value for your timerange in the past. If the timestamp column has a key the above addition will use it. Using unix_timestamp or mysql functions in general prevent the usage of keys. Some exemptions to my last statement are the mysql functions such as between, MAX, MIN, etc. Use the keyword explain before the select to get an idea if the above suggestion works. -----Original Message----- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Friday, March 28, 2003 9:12 AM To: mysql List Subject: Re: MySQL, Snort & ACID Dusty Hall wrote: >DB queries and >inserts are taking a very long time to complete. Speeding these up >would be my goal. Here's a snippet from my slow_queries log.... > ># Time: 030328 10:28:20 ># [EMAIL PROTECTED]: acid_user[acid_user] @ localhost [] ># Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 1353805 >SELECT acid_event.sid, acid_event.cid, signature, timestamp, >acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto FROM >acid_event WHERE ( UNIX_TIMESTAMP(timestamp) >= >UNIX_TIMESTAMP('2003-03-25 000000') AND UNIX_TIMESTAMP(time >stamp) <= UNIX_TIMESTAMP('2003-03-25 150000') ) LIMIT 0, 50; > > It looks to me as though your use of the UNIX_TIMESTAMP function is preventing any indexes that you might have from being used. I suggest that you phrase your queries without them, and index on timestamp if you haven't already. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
