?
Solved

MySQL: Indexes for Nested SELECT Statements

Posted on 2016-09-25
6
Medium Priority
?
71 Views
Last Modified: 2016-09-27
This is my query:
SELECT `CourseID`, `Name`, `EditVersion`, CourseID IN ( SELECT `Customer_Courses`.`CourseID` from `Customer_Courses` WHERE `ContactID`='123' ) AS X FROM `Courses` WHERE `Library`='c' AND `Available`='Yes' GROUP BY `Name` Order By `Name`

Open in new window

Here is the table structure:
CREATE TABLE `Courses` (
  `CourseID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Outline` varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Description` mediumtext COLLATE utf8_unicode_ci,
  `Notes` mediumtext COLLATE utf8_unicode_ci,
  `ReleaseDate` datetime(6) DEFAULT NULL,
  `keywords` mediumtext COLLATE utf8_unicode_ci,
  `Code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EditVersion` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Library` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bundle` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Available` varchar(50) COLLATE utf8_unicode_ci DEFAULT 'No',
  `Objectives` varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`CourseID`),
  UNIQUE KEY `CourseID` (`CourseID`),
  KEY `Library` (`Library`),
  KEY `Bundle` (`Bundle`),
  KEY `Available` (`Available`)
) ENGINE=InnoDB AUTO_INCREMENT=1329 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
	
CREATE TABLE `Customer_Courses` (
  `ContactID` int(11) NOT NULL,
  `CourseID` int(11) NOT NULL,
  KEY `ContactID` (`ContactID`),
  KEY `CourseID` (`CourseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Open in new window

What indexes could be added to increase the performance/speed of the query?
0
Comment
Question by:skij
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815151
Is the query slow? How many records do you have in each table?

If you'll never have more than a few hundred or even thousand courses, then the existing indexes for courses are probably ok. I'd guess you'd probably have quite a lot more records in customer_courses though.
0
 
LVL 10

Author Comment

by:skij
ID: 41815188
Currently the query is not slow, but now there are only a few hundred courses and a few hundred customer_courses.  Eventually there could be tens of thousands of customer_courses.
0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 500 total points
ID: 41815202
Which queries end up being slow may determine which indexes need to be added. Remember that only one index can be used on each table in a query, so if you have a filter

where columna = x and columnb = y

then even though you might have indexes on each column, only one index can be used. If that's not enough (eg if there are hundreds or thousands of records where columna = x) then you actually need an index on both columns (ie on columna, columnb) combined to give good performance, especially if joins to other tables increase the complexity of the query.

The query you've given as an example is relatively simple, and probably isn't worth adding further indexes for.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815204
When you have a multiple-column index, eg columna, columnb, columnc then the order of the columns matters if you don't always filter on all the columns.

eg as I understand it that index won't be used if you aren't filtering on columna (just columnb and columnc), but it can still be used if you are filtering on columna and columnb but not columnc.
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41815208
Your in clause and the order by are the culprit here !

try below query

SELECT * FROM 
(
	SELECT `CourseID`, `Name`, `EditVersion`, CourseID 
	AS X FROM `Courses` WHERE `Library`='c' AND `Available`='Yes' 
	GROUP BY `Name` 
)s
INNER JOIN 
(
	SELECT `CourseID` from `Customer_Courses` WHERE `ContactID`='123'
)r
ON r.`CourseID` = s.`CourseID`
Order By `Name`

--

Open in new window

--
0
 
LVL 79

Accepted Solution

by:
arnold earned 1000 total points
ID: 41815464
Since you are sorting on  Name, one option is to add an index for it. One would think it can be unique, but if courses are added, with old ones retired, a non-unique index...

Though not sure why you are not requiring a name, defaulting to null. Sorting with a possibility of a null ....
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question