Solved

MySQL: Indexes for Nested SELECT Statements

Posted on 2016-09-25
6
40 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
6 Comments
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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 16

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
Comment Utility
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 76

Accepted Solution

by:
arnold earned 250 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

7 Experts available now in Live!

Get 1:1 Help Now