Page 1 of 1

[SOLVED]optimizing search query

Posted: Sat Jun 11, 2005 4:07 pm
by thallish
Hi all

I have this problem that my search query performs so slow!!! it takes around 11 sec to perform a search and i can't believe that it is the fastest

EDIT: Ok i'm just gonna explain what i want to do, with this query.

I have an employee with some experience and some education and some personal data. I want to make a search that will return all the first and lastname of employees that have the searchstring in either education, course, certificat or in their experience with a technology or in their personal data.

Here is a dump of my database structure:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jun 11, 2005 at 10:56 PM
-- Server version: 4.0.23
-- PHP Version: 4.3.10
-- 
-- Database: `kompetence`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `certificat`
-- 

CREATE TABLE `certificat` (
  `certificatId` smallint(6) NOT NULL auto_increment,
  `title` varchar(40) NOT NULL default '',
  `expirationDate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`certificatId`),
  KEY `title` (`title`),
  KEY `certificatId` (`certificatId`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `client`
-- 

CREATE TABLE `client` (
  `clientId` smallint(6) NOT NULL auto_increment,
  `clientName` varchar(25) NOT NULL default '',
  `clientDescription` text NOT NULL,
  PRIMARY KEY  (`clientId`),
  KEY `clientName` (`clientName`),
  KEY `clientId` (`clientId`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `competence`
-- 

CREATE TABLE `competence` (
  `competenceId` smallint(6) NOT NULL auto_increment,
  `experienceId` smallint(6) NOT NULL default '0',
  `educationId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`competenceId`),
  KEY `competenceId` (`competenceId`),
  KEY `experienceId` (`experienceId`),
  KEY `educationId` (`educationId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `course`
-- 

CREATE TABLE `course` (
  `courseId` smallint(6) NOT NULL auto_increment,
  `courseOffer` varchar(40) NOT NULL default '',
  `title` varchar(40) NOT NULL default '',
  `duration` tinyint(4) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`courseId`),
  KEY `title` (`title`),
  KEY `courseId` (`courseId`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `course_education`
-- 

CREATE TABLE `course_education` (
  `id` smallint(6) NOT NULL auto_increment,
  `courseId` smallint(6) NOT NULL default '0',
  `educationId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `educationId` (`educationId`),
  KEY `courseId` (`courseId`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `education`
-- 

CREATE TABLE `education` (
  `educationId` smallint(6) NOT NULL auto_increment,
  PRIMARY KEY  (`educationId`),
  KEY `educationId` (`educationId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `education_certificat`
-- 

CREATE TABLE `education_certificat` (
  `id` smallint(6) NOT NULL auto_increment,
  `educationId` smallint(6) NOT NULL default '0',
  `certificatId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `educationId` (`educationId`),
  KEY `certificatId` (`certificatId`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `educationelement`
-- 

CREATE TABLE `educationelement` (
  `educationElementId` smallint(6) NOT NULL auto_increment,
  `exam` varchar(40) NOT NULL default '',
  `placeOfEducation` varchar(40) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `educationId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`educationElementId`),
  KEY `placeOfEducation` (`placeOfEducation`),
  KEY `exam` (`exam`),
  KEY `educationElementId` (`educationElementId`),
  KEY `educationId` (`educationId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `employee`
-- 

CREATE TABLE `employee` (
  `employeeId` smallint(6) NOT NULL auto_increment,
  `firstname` varchar(15) NOT NULL default '',
  `lastname` varchar(30) NOT NULL default '',
  `socialSkills` text NOT NULL,
  `desiredAreasOfWork` text NOT NULL,
  `competenceId` smallint(6) default '0',
  `isActivated` set('yes','no') NOT NULL default '',
  PRIMARY KEY  (`employeeId`),
  KEY `competenceId` (`competenceId`),
  KEY `employeeId` (`employeeId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `employee_project`
-- 

CREATE TABLE `employee_project` (
  `id` tinyint(4) NOT NULL auto_increment,
  `employeeId` smallint(6) NOT NULL default '0',
  `projectId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `employeeId` (`employeeId`),
  KEY `projectId` (`projectId`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `experience`
-- 

CREATE TABLE `experience` (
  `experienceId` smallint(6) NOT NULL auto_increment,
  PRIMARY KEY  (`experienceId`),
  KEY `experienceId` (`experienceId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `experienceelement`
-- 

CREATE TABLE `experienceelement` (
  `experienceElementId` smallint(6) NOT NULL auto_increment,
  `technologyKeywords` varchar(50) NOT NULL default '',
  `description` text NOT NULL,
  `levelOfKnowlegde` varchar(30) NOT NULL default '',
  `experienceId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`experienceElementId`),
  KEY `technologyKeywords` (`technologyKeywords`),
  KEY `experienceElementId` (`experienceElementId`)
) TYPE=MyISAM AUTO_INCREMENT=15 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `project`
-- 

CREATE TABLE `project` (
  `projectId` smallint(6) NOT NULL auto_increment,
  `projectDescription` text NOT NULL,
  `projectName` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`projectId`),
  KEY `projectId` (`projectId`),
  KEY `projectName` (`projectName`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `project_client`
-- 

CREATE TABLE `project_client` (
  `id` tinyint(4) NOT NULL auto_increment,
  `projectId` smallint(6) NOT NULL default '0',
  `clientId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `projectId` (`projectId`),
  KEY `clientId` (`clientId`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `project_experienceelement`
-- 

CREATE TABLE `project_experienceelement` (
  `id` smallint(6) NOT NULL auto_increment,
  `projectId` smallint(6) NOT NULL default '0',
  `experienceElementId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `experienceElementId` (`experienceElementId`),
  KEY `projectId` (`projectId`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `user`
-- 

CREATE TABLE `user` (
  `userId` smallint(6) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `isActivated` set('yes','no') NOT NULL default '',
  PRIMARY KEY  (`userId`),
  KEY `username` (`username`),
  KEY `isActivated` (`isActivated`),
  KEY `userId` (`userId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `user_employee`
-- 

CREATE TABLE `user_employee` (
  `id` smallint(6) NOT NULL auto_increment,
  `userId` smallint(6) NOT NULL default '0',
  `employeeId` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `userId` (`userId`),
  KEY `employeeId` (`employeeId`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;

and here is my search query:

Code: Select all

SELECT	DISTINCT	employee.employeeId,
												employee.firstname,
												employee.lastname
								FROM			user,
												user_employee,
												employee,
												competence,
												educationelement,
												experienceelement,
												certificat,
												education_certificat,
												course,
												course_education
								
								WHERE		competence.competenceId=employee.competenceId	
								
								AND			user.isActivated='yes'
								AND			user.userId=user_employee.userId
								AND			user_employee.employeeId=employee.employeeId
								
								AND			((employee.socialSkills LIKE '%$searchstring%' OR employee.desiredAreasOfWork LIKE '%$searchstring%')
								
								OR			((educationelement.exam LIKE '%$searchstring%' OR educationelement.placeOfEducation LIKE '%$searchstring%' )
								AND 			educationelement.educationId=competence.educationId) 
								
								OR			(experienceelement.technologyKeywords LIKE '%$searchstring%' 
								AND 			experienceelement.experienceId=competence.experienceId) 
								
								OR			(certificat.title LIKE '%$searchstring%' 
								AND 			certificat.certificatId=education_certificat.certificatId 
								AND 			education_certificat.educationId=competence.educationId )
								
								OR			(course.title LIKE '%$searchstring%' 
								AND 			course.courseId=course_education.courseId 
								AND 			course_education.educationId=competence.educationId))
								
								ORDER BY	employee.firstname ASC
My question is:

How can i optimize this? It needs this badly and i can't find the answers :(

Any help is strongly appreciated! thanks

/thallish

PS: And sorry for the long post, but i believe its needed. If not tell me and i will edit it :wink:

Posted: Sun Jun 12, 2005 5:05 am
by thallish
OK i got it quite optimized the query found a post on dev.mysql.com that stated that the use of OR in a query in the form

Code: Select all

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';
will slow down the search even if indices are used. Instead the query should be of the form

Code: Select all

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';
So thats what i did, and it helped tremendiously! the old query performd around 6.7995 sec and the new is 0.0115 sec. so thats the difference i wanted :wink:

Here is my new query

Code: Select all

SELECT employee.employeeId,
       employee.firstname,
       employee.lastname
FROM   user,
       user_employee,
       employee
WHERE  user.isActivated='yes'
AND    user.userId=user_employee.userId
AND    user_employee.employeeId=employee.employeeId
AND    employee.socialSkills LIKE '%$searchstring%'

UNION
	
SELECT employee.employeeId,
       employee.firstname,
       employee.lastname
FROM   user,
       user_employee,
       employee
WHERE  user.isActivated='yes'
AND    user.userId=user_employee.userId
AND    user_employee.employeeId=employee.employeeId
AND    employee.desiredAreasOfWork LIKE '%$searchstring%'

UNION
	
SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM            user,
                user_employee,
                employee,
                competence,
                educationelement
WHERE           user.isActivated='yes'
AND             user.userId=user_employee.userId
AND             user_employee.employeeId=employee.employeeId
AND             competence.competenceId=employee.competenceId
AND             educationelement.educationId=competence.educationId
AND             educationelement.exam LIKE '%$searchstring%'

UNION
	
SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM            user,
                user_employee,
                employee,
                competence,
                educationelement
WHERE           user.isActivated='yes'
AND             user.userId=user_employee.userId
AND             user_employee.employeeId=employee.employeeId
AND             competence.competenceId=employee.competenceId
AND             educationelement.educationId=competence.educationId
AND             educationelement.placeOfEducation LIKE '%$searchstring%'
	
UNION
	
SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM            user,
                user_employee,
                employee,
                competence,
                experienceelement
WHERE           user.isActivated='yes'
AND             user.userId=user_employee.userId
AND             user_employee.employeeId=employee.employeeId
AND             competence.competenceId=employee.competenceId
AND             experienceelement.technologyKeywords LIKE '%$searchstring%' 
AND             experienceelement.experienceId=competence.experienceId
 		
UNION	
	
SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM            user,
                user_employee,
                employee,
                competence,
                education_certificat,
                certificat
WHERE           user.isActivated='yes'
AND             user.userId=user_employee.userId
AND             user_employee.employeeId=employee.employeeId
AND             competence.competenceId=employee.competenceId
AND             certificat.title LIKE '%$searchstring%' 
AND             certificat.certificatId=education_certificat.certificatId 
AND             education_certificat.educationId=competence.educationId

UNION
	
SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM            user,
                user_employee,
                employee,
                competence,
                course_education,
                course
WHERE           user.isActivated='yes'
AND             user.userId=user_employee.userId
AND             user_employee.employeeId=employee.employeeId
AND             competence.competenceId=employee.competenceId
AND             course.title LIKE '%$searchstring%' 
AND             course.courseId=course_education.courseId 
AND             course_education.educationId=competence.educationId

Well into the fire once again

cheers
/thallish

Posted: Sun Jun 12, 2005 10:58 am
by Chris Corbyn
It's a good job you're as organised about writing your queries as you are....
That's one scarily big query 8O

Posted: Sun Jun 12, 2005 11:36 am
by anjanesh
Thanks thallish for the Info. It was very useful.
Did not know UNION would reduce the time so much.

Posted: Sun Jun 12, 2005 12:15 pm
by timvw
It's on of those items that is very often mentionned in a chapter on "SQL optimisation..."