Avatar of Matthew_Way
Matthew_Way

asked on 

MySQL View performance

I'm writing a PHP / MySQL application.
PHP 5.2 / MySQL 5.1
It's rather large and there are 34 MySQL tables.
I found that I had the same SQL statements with table joins being repeated in different parts of the application.
So I'm using views, which is good from a number of programing perspectives.
But my concern is performance it's not an issue at the moment because the database is relatively empty.
For example the attached view I run the following query.
SELECT *
FROM view_contact_assignedcontract
WHERE contactId=123

Which works fine with a test db of 100 records.
Does the SQL server need first create a temporary result set of all Contact records before it pulls out record #123 ?
Am I going to have performance issues as the database grows ?
CREATE OR REPLACE VIEW view_contact_assignedcontract AS
SELECT
-- Contact Info
  ct.ContactId AS contactId,
  CONCAT( ct.GivenNameEN,' ',ct.FamilyNameEN) AS contactNameEN,
  CONCAT( ct.FamilyNameZH,' ',ct.GivenNameZH) AS contactNameZH,
-- Company Info
  cp.CompanyId AS companyId,
  cp.CompanyNameEN AS companyNameEN,
  cp.CompanyNameZH AS companyNameZH,
-- Contract Info
  ctr.ContractId AS contractId,
  ctr.ContractName AS contractName,
  ctr.ContractNumber AS contractNumber,
  ctr.ContractCompleted AS contractCompleted
FROM contact ct
  LEFT JOIN company cp ON ct.Company_FK = cp.CompanyId
  LEFT JOIN contract ctr ON ct.AssignedContract = ctr.ContractId

Open in new window

MySQL Server

Avatar of undefined
Last Comment
Matthew_Way
Avatar of fosiul01
fosiul01
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi, read the below article about optimizing your mysql query. it should help you with your questions

http://www.whenpenguinsattack.com/2007/04/09/10-tips-for-optimizing-mysql-queries/

http://www.databasejournal.com/features/mysql/article.php/1382791
ASKER CERTIFIED SOLUTION
Avatar of Matthew_Way
Matthew_Way

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo