current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a table containing servers logs, and another table containing rules to match certain and drop them.

The logs tables contains around 1 million rows, and there are about 20-30 rules.

The query runs very slowly, I wonder is there any way I can make it run faster. I tried adding indexes to logs.message, but it does not help, I also read that you cannot index a "LIKE" column.

I am a total newbie to database, so please forgive me if I am missing any important concepts. Thanks in advance.

CREATE TABLE `logs` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `criticality` varchar(255) NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `message` varchar(4096) NOT NULL,
  `record_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `record_by` varchar(255) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_message` (`message`(255))
) ENGINE=InnoDB AUTO_INCREMENT=233523 DEFAULT CHARSET=utf8$$

CREATE TABLE `rules` (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  `type` enum('MATCH','DROP') NOT NULL DEFAULT 'DROP',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8$$

select 
    hostname, criticality, source, message, record_date
from
    eventlog.logs l1
where
    not exists( SELECT 
            l.message, r.rule_id
        FROM
            eventlog.logs l,
            eventlog.rules r
        where
            l.message like r.content
                and l.log_id = l1.log_id
                and r.type = 'DROP')
        and (criticality = 'High'
        or criticality = 'Medium')
        and record_date > sysdate() - Interval 2 Day
order by l1.message;

UPDATE 1 explain results, it took around 10 seconds to finish the query.

+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                |  rows   | Extra                       |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
|  1 | PRIMARY            | l1    | ALL    | NULL          | NULL    | NULL    | NULL               | 2101642 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | r     | ALL    | NULL          | NULL    | NULL    | NULL               |      16 | Using where                 |
|  2 | DEPENDENT SUBQUERY | l     | eq_ref | PRIMARY       | PRIMARY | 8       | eventlog.l1.log_id |       1 | Using where                 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
share|improve this question
    
Can you add some info on how long this query is taking? You will also certainly be asked to use the EXPLAIN statement dev.mysql.com/doc/refman/5.0/en/using-explain.html in order to help other people make a better diagnosis. –  KookieMonster Nov 26 '13 at 8:54
    
I have added explain to the question. Is there any general guide line to sql optimization? e.g. adding all columns in where clause to index? I am very new to DB and using this for learning and practise only, so I am open to any suggestions and testings. I read about full text search, soundex, etc. are these features applicable to this query? In general, if text matching took so long and so much cpu power to complete. how did the search engines do it? –  whattimeisit Nov 26 '13 at 9:28
    
And why do you have eventlog.logs in the subquery? You link the subquery using the primary key so even if you remove it, you'll have same results. –  ypercube Jan 26 at 22:15

2 Answers 2

up vote 0 down vote

pick up those columns that have higher cardinality values. For example, on a one million row table, if you've a column whose cardinality is say 500000, then the query would return just 2 values. Also, identify the frequency (how frequent the values are repeating in a particular column) of each value in column. These should be a good start.

share|improve this answer
    
cardinality and repeatness? are they refering to the same thing? My main problem is to improve text search performance, would it be a good idea to convert text into individual words or tags, index that tag table, and then make search of random based on score on the tag table? –  whattimeisit Dec 10 '13 at 8:52
up vote 0 down vote

This query should do the trick:

SELECT hostname, criticality, source, message, record_date
FROM logs l
LEFT JOIN rules r ON l.message like r.content AND r.type = 'DROP'
WHERE (criticality = 'High'
        or criticality = 'Medium')
        and record_date > sysdate() - Interval 2 Day
        AND r.rule_id IS NULL
order by l.message;

but do not expect it to run fast, as long as you have a JOIN constraint using a like clause referencing two varchar columns, which do not even have an index on them.

Nevertheless it should be faster (in MySQL) as the exist subquery.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.