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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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                 |
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 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 '14 at 22:15

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

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.

share|improve this answer

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 replace the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN

share|improve this answer

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.

share|improve this answer

Your Answer


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.