Hi All experts,
I am facing problem with mysql view.
I have migrated my database from oracle to mysql qith views, View on Mysql is created , but when i try to execute query it takes a long timw , some times it time qout server connection, Any body help me urgent plz.

Keep in mind , all tables used in view are in database with data.

here is My View

CREATE OR REPLACE VIEW `ictm_proj_user` (COMPANY_ID, PROJECT_ID, PROJ_ID, USER_ID) AS
 SELECT ict.company_id,prj.project_id,usr.proj_id, usr.user_id
   FROM ictm_project ict, ictm_project_user usr, ictm_project_detail prj
 WHERE ((usr.proj_id = prj.proj_id) AND (prj.project_id = ict.project_id) )
 union all
 SELECT  ict.company_id,prj.project_id,prj.proj_id,prj.user_id
   FROM ictm_project ict, ictm_project_detail prj
  WHERE ((prj.project_id = ict.project_id))
 union all
 SELECT ict.company_id,ict.project_id, 'Main',ict.admin_id
   FROM ictm_project ict
   union all
 SELECT ict.company_id,ict.project_id,'Main', comp.admin_id
   FROM ictm_project ict, ictm_company comp
  WHERE ((comp.company_id = ict.company_id));

Even i tried to execute follwoing query its not responding

SELECT ict.company_id,prj.project_id,usr.proj_id, usr.user_id
   FROM ictm_project ict, ictm_project_user usr, ictm_project_detail prj
 WHERE ((usr.proj_id = prj.proj_id) AND (prj.project_id = ict.project_id) )
 union all
 SELECT  ict.company_id,prj.project_id,prj.proj_id,prj.user_id
   FROM ictm_project ict, ictm_project_detail prj
  WHERE ((prj.project_id = ict.project_id))
 union all
 SELECT ict.company_id,ict.project_id, 'Main',ict.admin_id
   FROM ictm_project ict
   union all
 SELECT ict.company_id,ict.project_id,'Main', comp.admin_id
   FROM ictm_project ict, ictm_company comp
  WHERE ((comp.company_id = ict.company_id));

i dont know where the problem is and how to solve it.

Thanks in advance for helping me .

Recommended Answers

All 3 Replies

Do you have indexes on the source table ?

I have converted from oracle tables to mysql , may be in oracle they have indexex , but in mysql all indexes from oracle are lost. kindly help me to solve it.
Thaks alot

You need to create indexes in the base tables.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.