MySQL: Indexes for Nested SELECT Statements

Posted on 2016-09-25
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` (
  `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`)
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?
Question by:skij
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.
LVL 10

Author Comment

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.
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 125 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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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.
LVL 24

Assisted Solution

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

try below query

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


Open in new window

LVL 77

Accepted Solution

arnold earned 250 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 ....

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now