1

I have the below tables in the MySQL database

create table course_class
(
    id                      varchar(36)   not null primary key,
    class_code              varchar(255)  not null,
    course_code             varchar(255)  null,
    description             longtext      null,
    end_date                datetime      null,
    name                    varchar(255)  null,
    start_date              datetime      null,
);

create table role
(
    id            varchar(36)  not null primary key,
    name          varchar(255) null,
);

create table user
(
    id             varchar(36)  not null primary key,
    first_name     varchar(255) not null,
    gender         varchar(255) null,
    last_name      varchar(255) not null,
    middle_name    varchar(255) null,
    username       varchar(255) not null,
);

create table course_class_user
(
    id                          varchar(36)  not null primary key,
    course_class_id             varchar(36)  not null,
    role_id                     varchar(36)  not null,
    user_id                     varchar(36)  not null,
    constraint FK_course_class_course_class_id
        foreign key (course_class_id) references course_class (id),
    constraint FK_course_class_user_role_id
        foreign key (role_id) references role (id),
    constraint FK_course_class_user_user_id
        foreign key (user_id) references user (id)
);

I have below requirement

As a user I need the ability to get all classes an instructor is teaching.

  • It should support partial name search, the partial text can be either in first_name or last_name. (UI has only one text field and they can give partial first_name or last_name or both)
  • If one word is sent look for a partial match either in first_name or in last_name.
  • If both are provided, look for the partial match of both words in both fields.
  • Sort order shall be first_name and last_name

I am extracting firstName and lastName using the below code in java

String firstName = instructorName;
String lastName = StringUtils.SPACE;
if (instructorName.contains(StringUtils.SPACE)) {
    String[] fullName = instructorName.split(StringUtils.SPACE);
    firstName = fullName[0];
    lastName = fullName[1];
}

Variable instructorName holds user input, It can be Monika Smith or Smith Monika and for the above requirement, I have created the below query in java

@Query(value = "select cc from CourseClass cc "
        + "inner join CourseClassUser ccu on cc.id = ccu.courseClass.id "
        + "inner join Role r on ccu.role.id = r.id "
        + "inner join User u on ccu.user.id = u.id "
        + "where r.name = 'Instructor' and (u.firstName like %:firstName% or u.lastName like %:lastName% "
        + "or u.firstName like %:lastName% or u.lastName like %:firstName%) "
        + "order by u.firstName, u.lastName"
    )

It generates the below query for input smith monika

select cc from course_class cc
         inner join course_class_user ccu on cc.id = ccu.course_class_id
         inner join role r on ccu.role_id = r.id
         inner join user u on ccu.user_id = u.id
where r.name = 'Instructor' and (u.first_name like '%smith%' or u.last_name like '%monika%'
   or u.first_name like '%monika%' or last_name like '%smith%')
order by u.first_name, u.last_name;

and for input monika it generates the below query

select cc from course_class cc
         inner join course_class_user ccu on cc.id = ccu.course_class_id
         inner join role r on ccu.role_id = r.id
         inner join user u on ccu.user_id = u.id
where r.name = 'Instructor' and (u.first_name like '%monika%' or u.last_name like '% %'
   or u.first_name like '% %' or last_name like '%monika%')
order by u.first_name, u.last_name;

For now, the above query serves the purpose. I want to know if my query is efficient and correct or is there an alternate and efficient way to serve the requirement?

Update 1: Below is the output of EXPLAIN

+--+-----------+-----+----------+------+-----------------------------------------------------------------------------------------+-------------------------------+-------+---------------------+----+--------+-------------------------------+
|id|select_type|table|partitions|type  |possible_keys                                                                            |key                            |key_len|ref                  |rows|filtered|Extra                          |
+--+-----------+-----+----------+------+-----------------------------------------------------------------------------------------+-------------------------------+-------+---------------------+----+--------+-------------------------------+
|1 |SIMPLE     |cc   |NULL      |ALL   |PRIMARY                                                                                  |NULL                           |NULL   |NULL                 |9   |100     |Using temporary; Using filesort|
|1 |SIMPLE     |ccu  |NULL      |ref   |idx_user_course_class_tenant,FK_course_class_user_role_id,FK_course_class_course_class_id|FK_course_class_course_class_id|146    |classroom.cc.id      |7   |100     |NULL                           |
|1 |SIMPLE     |r    |NULL      |eq_ref|PRIMARY                                                                                  |PRIMARY                        |146    |classroom.ccu.role_id|1   |10      |Using where                    |
|1 |SIMPLE     |u    |NULL      |eq_ref|PRIMARY                                                                                  |PRIMARY                        |146    |classroom.ccu.user_id|1   |37.57   |Using where                    |
+--+-----------+-----+----------+------+-----------------------------------------------------------------------------------------+-------------------------------+-------+---------------------+----+--------+-------------------------------+

Below is the output of EXPLAIN ANALYZE

-> Sort row IDs: u.first_name, u.last_name  (actual time=0.391..0.393 rows=6 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.004 rows=6 loops=1)
        -> Temporary table  (cost=39.00 rows=2) (actual time=0.366..0.370 rows=6 loops=1)
            -> Nested loop inner join  (cost=39.00 rows=2) (actual time=0.096..0.323 rows=6 loops=1)
                -> Nested loop inner join  (cost=36.76 rows=6) (actual time=0.076..0.291 rows=7 loops=1)
                    -> Nested loop inner join  (cost=14.31 rows=64) (actual time=0.069..0.215 rows=57 loops=1)
                        -> Table scan on cc  (cost=1.15 rows=9) (actual time=0.032..0.045 rows=9 loops=1)
                        -> Index lookup on ccu using FK_course_class_course_class_id (course_class_id=cc.id)  (cost=0.83 rows=7) (actual time=0.016..0.018 rows=6 loops=9)
                    -> Filter: (r.`name` = 'Instructor')  (cost=0.25 rows=0) (actual time=0.001..0.001 rows=0 loops=57)
                        -> Single-row index lookup on r using PRIMARY (id=ccu.role_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=57)
                -> Filter: ((u.first_name like '%fr%') or (u.last_name like '% %') or (u.first_name like '% %') or (u.last_name like '%fr%'))  (cost=0.26 rows=0) (actual time=0.004..0.004 rows=1 loops=7)
                    -> Single-row index lookup on u using PRIMARY (id=ccu.user_id)  (cost=0.26 rows=1) (actual time=0.003..0.003 rows=1 loops=7)

7
  • can you give a hint and tag which database you use. and you can also provide a explain of your queies
    – nbk
    Commented Jun 21, 2021 at 16:11
  • @nbk I am using MySQL I have updated the tag.
    – Swapnil
    Commented Jun 21, 2021 at 16:18
  • please add also EXPLAIN your Query
    – nbk
    Commented Jun 21, 2021 at 16:48
  • thus explain dev.mysql.com/doc/refman/8.0/en/explain.html :)
    – nbk
    Commented Jun 21, 2021 at 16:53
  • @nbk I have added output of the Explain and Explain Analyze
    – Swapnil
    Commented Jun 21, 2021 at 18:22

0

Your Answer

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

Browse other questions tagged or ask your own question.