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.

  `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_by` varchar(255) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_message` (`message`(255))

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`)

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                 |
  • 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. Nov 26, 2013 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? Nov 26, 2013 at 9:28
  • 1
    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. Jan 26, 2014 at 22:15

4 Answers 4


I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed:

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

I also replaced the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN.


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.

  • 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? Dec 10, 2013 at 8:52

As the results of the query will be the same for existing data (unless you change the rules), have you thought of storing the results of your query in another table along with the max log_id value from the source? That way, when analysing the next chunk of data, you can add a filter to the query to select only data with log_id values higher than those you've already analysed, and then add that output to your summary table.


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) than the exists subquery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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