Question 396187 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to convert an sql view to table

I have a view and its taking longer time to execute so i need to write simple query to rather than view.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `view_party_make_subcat_size_map` AS 
    select  distinct `m`.`part_name` AS `material_name`,`pt`.`pk_id` AS `pk_id`,
            `pms`.`fk_party_id` AS `fk_party_id`,`pms`.`fk_party_type_id` AS `fk_party_type_id`,
            `k`.`pk_id` AS `fk_make_id`,`pms`.`fk_sub_cat_id` AS `fk_sub_cat_id`,
            `pms`.`fk_size_id` AS `fk_size_id`,`pms`.`status` AS `status`,
            `pms`.`fk_financial_year_id` AS `fk_financial_year_id`,
            `pms`.`fk_company_id` AS `fk_company_id`,`pms`.`quantity` AS `quantity`,
            `k`.`name` AS `make_name`,`sc`.`name` AS `sub_cat_name`,
            `pt`.`name` AS `party_name`,`pt`.`fk_city_id` AS `fk_city_id`,
            `pt`.`fk_state_id` AS `fk_state_id`,`pt`.`company_type` AS `company_type`,
            `pt`.`fk_area` AS `fk_area`,`hsn`.`hsn_name` AS `hsn_name`,
            `m`.`fk_hsn_id` AS `fk_hsn_id`,`size`.`name` AS `size_name`,
            `pt`.`address` AS `party_address`
    from (((((((`ma_party_make_subcat_map` `pms` 
    join `ma_company` `pt` on((`pt`.`pk_id` = `pms`.`fk_party_id`))) 
    left join `ma_sub_category` `sc` on((`sc`.`pk_id` = `pms`.`fk_sub_cat_id`))) 
    join `ma_make` `k` on((`k`.`pk_id` = `pms`.`fk_make_id`))) 
    join `ma_material_make_map` `mm` on((`mm`.`fk_make_id` = `k`.`pk_id`))) 
    join `ma_material` `m` on((`m`.`pk_id` = `mm`.`fk_material_id`))) 
    left join `ma_hsn_tax_map` `hsn` on((`m`.`fk_hsn_id` = `hsn`.`fk_hsn_id`))) 
    join `ma_size` `size` on((`size`.`pk_id` = `pms`.`fk_size_id`))) 
    where (`pms`.`status` = 2)

I created a view using this query and its perfomance is too slow to optimize the performance i need to convert this into a simple query and store the result in a table

the query is having multiple joins and making the perfomance slower so i thought it is good to go with simpler query to select values from each table and then store in a new table

I am expecting optimised way for this query for better performance

Tags:

2

Answers


  1. 0

    i need to convert this into a simple query and store the result in a table

    A sample

    -- create some source table
create table t1 select 1 id, 1 val, now() created_at union select 2, 2, now();
select * from t1;
-- create a view based on this table
create view t2 as select * from t1;
select * from t2;
    id val created_at
    1 1 2024-07-05 08:53:54
    2 2 2024-07-05 08:53:54
    id val created_at
    1 1 2024-07-05 08:53:54
    2 2 2024-07-05 08:53:54 
    -- create a table with the data returned by the view
create table t3 table t2;
select * from t3;
show create table t3;
    id val created_at
    1 1 2024-07-05 08:53:54
    2 2 2024-07-05 08:53:54
    Table Create Table
    t3 CREATE TABLE `t3` (
      `id` bigint NOT NULL DEFAULT ‘0’,
      `val` bigint NOT NULL DEFAULT ‘0’,
      `created_at` datetime NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    		 
    -- create a table with the data returned by the view
-- and specify / adjust its structure
create table t4 ( id int unsigned primary key,
                  val varchar(10),
                  grp varchar(10) default 'unknown'
                  ) table t2;
select * from t4;
show create table t4;
    grp id val created_at
    unknown 1 1 2024-07-05 08:53:54
    unknown 2 2 2024-07-05 08:53:54
    Table Create Table
    t4 CREATE TABLE `t4` (
      `grp` varchar(10) DEFAULT ‘unknown’,
      `id` int unsigned NOT NULL,
      `val` varchar(10) DEFAULT NULL,
      `created_at` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    fiddle

    Login or Signup to reply.
  2. 0

    The CREATE TABLE and SELECT can be done in a single statement

    CREATE TABLE all_stuff (
        -- no need to list columns; they will be derived from the SELECT
        PRIMARY KEY(...)   -- decide what it will be and add it here
    ) AS   SELECT ... -- the SELECT part of the CREATE VIEW.
;

    Creating a table just to run a query will not be efficient.

    Let’s see one of your problematic SELECTs against the VIEW, plus EXPLAIN SELECT. We may be able to work with that to help improve the performance.

    What version of MySQL are you running?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search