Hibernate query execution issue

Question

I am facing some issue in query execution here is my case :

I have two tables log with 2 lakh records and logrecords with 6 lakh records

Where single log record in log table can have multiple log messages in logrecords table my database schema is as below

log Table

CREATE TABLE `log` (                           
       `logid` varchar(50) NOT NULL DEFAULT '',         
       `creationtime` bigint(20) DEFAULT NULL,            
       `serviceInitiatorID` varchar(50) DEFAULT NULL,   
       PRIMARY KEY (`logid`),                           
       KEY `idx_creationtime_wsc_log` (`creationtime`)  
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1

And logrecords Table

CREATE TABLE `logrecords` (                                                 
              `logrecordid` varchar(50) NOT NULL DEFAULT '',                                
              `timestamp` bigint(20) DEFAULT NULL,                                          
              `message` varchar(8000) DEFAULT NULL,                                         
              `loglevel` int(11) DEFAULT NULL,                                              
              `logid` varchar(50) DEFAULT NULL,                                             
              `indexcolumn` int(11) DEFAULT NULL,                                           
              PRIMARY KEY (`logrecordid`),                                                  
              KEY `indx_logrecordid_message_logid` (`logrecordid`,`message`(767),`logid`),  
              KEY `logid` (`logid`),                                                        
              KEY `indx_message` (`message`(767))                                           
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Query created by hibernate is like

select this_.logid as logid4_1_, this_.loglevel as loglevel4_1_, this_.creationtime as creation3_4_1_,this_.serviceInitiatorID as service17_4_1_, this_.logtype as logtype4_1_,logrecord1_.logrecordid as logrecor1_3_0_, logrecord1_.timestamp as timestamp3_0_, logrecord1_.message as message3_0_, logrecord1_.loglevel as loglevel3_0_, logrecord1_.logid as logid3_0_, logrecord1_.indexcolumn as indexcol6_3_0_ from log this_ inner join wsc_logrecords logrecord1_ on this_.logid=logrecord1_.logid where (1=1) and (1=1) and logrecord1_.message like 'SecondMessage' order by this_.creationtime desc limit 25

Which taking around 7313ms to execute

Query Explain is like

enter image description here

But when I execute below query it is taking around 15 min to execute

select count(*) as y0_ from log this_ inner join logrecords logrecord1_ on this_.logid=logrecord1_.logid where (1=1) and (1=1) and lower(logrecord1_.message) like 'SecondMessage' order by this_.creationtime desc limit 25

For above query explain is like enter image description here

and I am using MySQl database. I think there is some issue in indexing or some other which I am not able to identify

Any solution will be appreciated.

if you write explain `before` query, what does it tell you? – Shervin Asgari Apr 08 '13 at 14:14 — Shervin Asgari, Apr 08 '13 at 14:14
Hi Shervin i have updated my question with query Explain – Harshil Apr 09 '13 at 04:32 — Harshil, Apr 09 '13 at 04:32

score 0 · Answer 1 · answered Apr 08 '13 at 14:19

0

When you use lower(logrecord1_.message) like 'SecondMessage' instead of plain logrecord1_.message like 'SecondMessage' the DB engine will stop using the index on logrecord1_.message.

You can overcome this by creating a function based index that has lower(logrecord1_.message) in place of logrecord1_.message.

answered Apr 08 '13 at 14:19

Klas Lindbäck

33,105
5
57
82

MySQL doesn't support function-based indexes, as described here: http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql However, I think by using a case-insensitive collation for column message, I think the lower() function becomes redundant. – Marcellus Apr 08 '13 at 14:25
@Marcellus: Write it up as an answer. I'll delete this answer in a bit since it isn't helpful. – Klas Lindbäck Apr 08 '13 at 14:49
I also try with logrecord1_.message like 'SecondMessage' instead of using lower(logrecord1_.message) like 'SecondMessage' in count query but no any improvement. – Harshil Apr 09 '13 at 07:29

Hibernate query execution issue

1 Answers1