Hi Jenn, I suggest you use of this query : SELECT host, date, time, message FROM syslogTB WHERE host = '$host' ORDER BY date DESC,time DESC LIMIT " . $start . ", " . $numresults . "");
at the second stage, try adding an index date_time on syslogTB(date DESC,time DESC, host); Mathias Selon Jennifer Fountain <[EMAIL PROTECTED]>: > Here how I created the table: > > CREATE TABLE syslogTB ( > facility char(10), # OPTIONAL field for facility > priority char(10), # OPTIONAL field for priority > date date, # date of this log message > time time, # time of this message > host varchar(128), # host logging, If you have a host with > # 128 characters you probably > # have other issues to worry about than > #someone being l33t. 8-) > message text, > INDEX host_index (host), > INDEX date_index (date), > INDEX message_index (message (50)) , #Index the first 50 characters > seq int unsigned auto_increment primary key # optional sequencenumber > ); > > > And here is the query > SELECT host, date, time, message FROM syslogTB WHERE host = '$host' AND > date LIKE '%$date%' AND message LIKE '%$message%' ORDER BY date > DESC,time DESC LIMIT " . $start . ", " . $numresults . ""); > > > > Thanks > Jenn > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Saturday, May 21, 2005 1:56 PM > To: Jennifer Fountain > Cc: mysql@lists.mysql.com > Subject: Re: Query Issue on Large DB > > Hi, > have you a query sample + show create table <TBL> + show indexes from > <TBL> ? > > Mathias > > Selon Jennifer Fountain <[EMAIL PROTECTED]>: > > > Hi, > > I am new to the list and mysql for that matter. I am trying to > > configure a centralized syslog server using mysql, msyslog and php. > > Works great until the DB grows to about 2GB. After the database gets > > over 2GB, running queries on a particular hostname (that has a lot of > > records) takes a long time and drains memory to the point I have to > > reboot the system. Here is the status after a single large query is > > ran: > > > > total used free shared buffers > > cached > > Mem: 3889616 3871504 18112 0 21336 > > 3654652 > > -/+ buffers/cache: 195516 3694100 > > Swap: 4194232 0 4194232 > > > > > > > > Here is a copy of my.cnf: > > > > [mysqld] > > datadir=/data > > socket=/var/lib/mysql/mysql.sock > > skip-locking > > set-variable = key_buffer_size=384M > > set-variable = max_allowed_packet=1M > > set-variable = table_cache=512 > > set-variable = sort_buffer=2M > > set-variable = record_buffer=2M > > set-variable = thread_cache=8 > > set-variable = read_rnd_buffer_size=2M > > # Try number of CPU's*2 for thread_concurrency > > set-variable = thread_concurrency=8 > > set-variable = myisam_sort_buffer_size=64M > > log-bin > > server-id = 1 > > > > [mysql.server] > > user=mysql > > basedir=/var/lib > > > > [safe_mysqld] > > err-log=/var/log/mysqld.log > > pid-file=/var/run/mysqld/mysqld.pid > > > > > > [mysqldump] > > quick > > set-variable = max_allowed_packet=16M > > > > [mysql] > > no-auto-rehash > > # Remove the next comment character if you are not familiar with SQL > > #safe-updates > > > > [isamchk] > > set-variable = key_buffer=256M > > set-variable = sort_buffer=256M > > set-variable = read_buffer=2M > > set-variable = write_buffer=2M > > > > [myisamchk] > > set-variable = key_buffer=256M > > set-variable = sort_buffer=256M > > set-variable = read_buffer=2M > > set-variable = write_buffer=2M > > > > [mysqlhotcopy] > > interactive-timeout > > > > Any thoughts as to what I am missing? Thanks for any information! > > > > Kind Regards, > > > > Jennifer Fountain > > Systems Administrator/Security > > R&B Distribution > > 3400 E Walnut Street > > Colmar, PA 18915 > > > > -- > > 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]
