CODEWITHSUNDEEP
mysql
UMAIR ALI
UMAIR ALI

Reputation: 1115

How to decrease query execution time MySql

this is my Mysql database view.

 Create View viewschools As Select
  school.*,
  conference.conference_name,
  conference.division_id,
  division.division_level,
  facility.facility_address,
  GROUP_CONCAT(DISTINCT facility.facility_name) AS Athletic_Venue
FROM school
  INNER JOIN conference
    ON school.conference_id = conference.conference_id
  INNER JOIN division
    ON conference.division_id = division.division_id
  LEFT OUTER JOIN schoolfacilitysport
    ON school.school_id = schoolfacilitysport.school_id
  LEFT OUTER JOIN facilitytosports
    ON schoolfacilitysport.facilitytosports_id = facilitytosports.facilitytosports_id
  LEFT OUTER JOIN facility
    ON facilitytosports.facility_id = facility.facility_id
    group by school.school_id

in database tables 20 thousands record now query execution time is 13 second then i open this view in database. Query execution time increase when search any school name with like operator. Please suggest any thing to decrease query execution time.

Upvotes: 1

Views: 1112

Answers (2)

IshaS
IshaS

Reputation: 837

Use mysql indexes. following link may helpful. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Upvotes: 0

sqlab
sqlab

Reputation: 6436

First create indices on conference_id, division_id, school_id, facilitytosports_id, facility_id if you have not done already.
See create index

Upvotes: 1

Related Questions