Solved

MySQL: Indexes for Nested SELECT Statements

Posted on 2016-09-25
6
66 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 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.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 125 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 78

Accepted Solution

by:
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 ....
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 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