now i'm trying to figure out, what should i do, to improve my query result. Now, it's 47.55. So, should i create any indexes for columns? Tell me please

SELECT bw.workloadId, lrer.lecturerId, lrer.lastname, lrer.name, lrer.fathername, bt.title, ac.activityname, cast(bw.exactday as char(45)) as "date", bw.exacttime as "time" FROM base_workload as bw
        right join unioncourse as uc on uc.idunioncourse = bw.idunioncourse
        right join basecoursea as bc on bc.idbasecoursea = uc.idbasecourse
        right join lecturer as lrer on lrer.lecturerId = uc.lecturerId
        right join basetitle as bt on bt.idbasetitle = bc.idbasetitle
        right join activity as ac on ac.activityId = bc.activityId
        where lrer.lecturerId is not null AND bc.idbasecoursea is not null and bw.idunioncourse != ""
        ORDER BY bw.exactday, bw.exacttime ASC;

CodePudding user response：

From MySQL 8.0 documentation:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

MySQL use indexes for these operations:

• To find the rows matching a WHERE clause quickly.
• To eliminate rows from consideration.
• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
• To retrieve rows from other tables when performing joins.
• To find the MIN() or MAX() value for a specific indexed column key_col.
• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1, key_part2).
• In some cases, a query can be optimized to retrieve values without consulting the data rows.

As of your requirements, you could use index on the WHERE clause for faster data retrieval.

CodePudding user response：

I think you can get rid of

           lrer.lecturerId is not null
      AND  bc.idbasecoursea is not null

By changing the first 3 RIGHT JOINs to JOINs.

What the datatype of exactday? What is the purpose of

cast(bw.exactday as char(45)) as "date"

The CAST may be unnecessary.

Re bw.exactday, bw.exacttime: It is usually better to use a single column for DATETIME instead of two columns (DATE and TIME).

What are the PRIMARY KEYs of the tables?

Please convert to LEFT JOIN if possible; I can't wrap my head around RIGHT JOINs.

This index on bw may help: INDEX(exactday, exacttime).