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 ASCHow 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