Java - MariaDB Perfoming slow with Hibernate criteria

Question

Environment:

mariadb-java-client-2.7.0

DB : MariaDB 10.5.7

ojdbc8 - Oracle 11.2.0.3.0 JDBC 4.0

DB : Oracle Database 11g

Hibernate 4.3.8

Code :

        Session session = sessionFactory.openSession();
        Criteria fetchCriteria = session.createCriteria("Student");
        
        Disjunction disjunction = Restrictions.disjunction();

        for (int i = 1; i <= 10000; i++) {
            Conjunction conjunction = Restrictions.conjunction();
            conjunction.add(Restrictions.eq("RollNumber", i+""));
            disjunction.add(conjunction);
        }
        
        fetchCriteria.add(disjunction);
          long start1 = System.currentTimeMillis();
        List  resultList = fetchCriteria.setFirstResult(0).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
         long end1 = System.currentTimeMillis();
         System.out.println("Time took :"+(end1-start1) +"ms");

Issue

1. If i run above code with Hibernate 4.3.8 + Oracle 8 it taking less than 5000 milliseconds.
2. If i run above code with Hibernate 4.3.8 +mariadb-java-client-2.7.0 it taking more than 40,000 milliseconds.

Extra Configuration : I have set hibernate.jdbc.fetch_size to 100 in hibernate.cfg.xml along with jdbc URL ,username and password.

Findings:

1. The query generated in both cases are same and if i execute those query with SQL Client it takes 10-11 seconds for ORACLE and 41-42 seconds for MariaDB.
2. The query which is generated by both database if i invoke using JDBC program (both for ORACLE and MariaDB) it is taking approx 600 milliseconds

Note: Both tables (Oracle and MariaDB) have 15,000 records.

Can anyone help me why MariaDB is taking time? or some extra settings are required to improve the MariaDB performance. I have tried defaultFetchSize which is mentioned in https://mariadb.com/kb/en/about-mariadb-connector-j/ but no luck.

SQL Query Generated by the databases:

select  this_.rollNo as RollNo1_0_0_, this_.VersionID as Version2_0_0_,
        this_.Name as Name3_0_0_, this_.dept as dept4_0_0_,
        this_.favSubj as favSubj5_0_0_,
        this_.ID as ID33_0_0_
    from  Student this_
    where  ((this_.ID='1')
              or  (this_.ID='2')
              or  (this_.ID='3')
              or  ....
              or  (this_.ID='10000') 

MariaDB DDL

CREATE TABLE `student` (
  `RollNo` bigint(20) NOT NULL ,
  `VersionID` bigint(20) NOT NULL,
  `Name` varchar(100) COLLATE ucs2_bin DEFAULT NULL,
  `dept` varchar(100) COLLATE ucs2_bin DEFAULT NULL,
  `favSubj` varchar(100) COLLATE ucs2_bin DEFAULT NULL,
  `ID` varchar(100) COLLATE ucs2_bin DEFAULT NULL,
   PRIMARY KEY (`RollNo`),
  UNIQUE KEY `UK_student` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20258138 DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin

Oracle DDL

CREATE TABLE student (
  RollNo NUMBER(19,0), 
  VersionID NUMBER(19,0) NOT NULL ENABLE,
  Name VARCHAR2(100),
  dept  VARCHAR2(100),
  favSubj VARCHAR2(100),
  ID VARCHAR2(100), 
  PRIMARY KEY ("RollNo"),
  CONSTRAINT "UK_student" UNIQUE ("ID")
)

MariaDB explain select query output

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	this_	range	UK_Student	UK_Student	203	NULL	10000	Using index condition

Answer 1

An OR with 10K items takes a long time to parse. Faster would be an IN:

 where  this_.ID IN ('1', '2', ..., '10000')

However, even that is likely to take a long time to run.

In the case of MariaDB, I think the Optimizer will say

• Oh, that's too many items for me to look up each one, so
• I will, instead, simply scan the table, checking each row for an ID in that list (using some kind of efficient lookup in the 10K-long list).

However, if there are 20M rows in the table, that will take a long time.

Can you provide the query plan (EXPLAIN) so we can confirm what I am hypothecating?

This seems logical and faster, but will not work correctly:

 where  this_.ID BETWEEN '1' AND '10000'

because it is a VARCHAR!!

Performance -- Make id an INT, not a VARCHAR!