How to add index to table view MySQL

Question

I have a problem with database performance because there are too many joins in the query that was built. I have the initiative to create a table view so that the process becomes faster than before, the code is as below

CREATE VIEW dashboard_sales AS (
    SELECT o.order_id,
           o.order_date,
           o.order_status,
           o.order_gender,
           o.order_birth_date,
           op.op_status,
           op.op_payment_code,
           oi.oi_qty,
           op.op_total,
           oi.item_id,
           i.item_name,
           dc.dc_id,
           dc.dc_name,
           dc.dc_sales,
           c.id_city,
           c.name_city,
           pc.pc_caption
    FROM `order` o 
    LEFT JOIN order_items oi
    ON o.order_id = oi.order_id
    LEFT JOIN order_payment op
    ON o.order_id = op.order_id
    LEFT JOIN item i
    ON oi.item_id = i.item_id
    LEFT JOIN distribution_channel dc
    ON o.dc_id = dc.dc_id
    LEFT JOIN city c
    ON o.order_city = c.id_city
    LEFT JOIN payment_channel pc
    ON op.op_payment_code = pc.pc_code
);

but because there are many records and I am looking for a solution that is by adding indexing as below,

CREATE INDEX MyIndex
ON dashboard_sales(op_total, order_date)

but i get some error like this,

#1347 - 'matoa_admin.dashboard_sales' is not BASE TABLE

how to solved this problem? and can it make indexing in the table view?

Mysql or what DB system? Order is not a good table name, you should rename that. — user3783243, Jun 13 '19 at 03:26
This doesn't seem to have anything to do with php, so why tag it with that? Have you tried researching an answer? — Ben, Jun 13 '19 at 03:26
The reference manual might point you in the right direction (https://mariadb.com/kb/en/library/create-index/) — , Jun 13 '19 at 03:28
can you check this answer it might help you? https://stackoverflow.com/questions/13944946/how-do-i-get-mysql-to-use-an-index-for-view-query — Jigar, Jun 13 '19 at 05:10

score 0 · Answer 1 · answered Jun 13 '19 at 05:33

You need to create index on table(s) not on to the view itself.

op_total and order_date is not the first concern here. You first need to makesure that on clauses on the main query is using indexed columns and if not you may need to create proper indexes for it.

I suggest you to use Explain plan in order to detect performance issues. And then you can have necessary actions.

The query by itself does not give us information and Explain plan is necessary for determining possible problems

How to add index to table view MySQL

1 Answers1