Solved

MySQL: Indexes for Nested SELECT Statements

Posted on 2016-09-25
6
54 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 28

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 77

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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 …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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