Timeline for Which columns should be indexed when all may be used in different search queries?
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 6, 2019 at 20:25 | comment | added | Visual Vincent | Thanks for your detailed answer and feedback! I'll be accepting Willem's answer as his explanation better helped me visualize how indexes work (at least in theory). I'd accept both of yours' if I could! ;) -- Oh and I'll study your article when I get the time (might even print it)! It looks very interesting! | |
| Feb 6, 2019 at 20:17 | comment | added | Willem Renzema | @RickJames No worries. When I saw the (very well articulated) question, and how simple it was (for people like you and me with experience with these questions/situations), I knew it was likely I would be racing someone. I actually figured it would probably be you, too. ;) | |
| Feb 6, 2019 at 20:11 | comment | added | Rick James | @VisualVincent -- Oops, I gave you the wrong link. I added the better link. (Both are useful to the discussion.) | |
| Feb 6, 2019 at 20:10 | history | edited | Rick James | CC BY-SA 4.0 | another link |
| Feb 6, 2019 at 20:07 | comment | added | Rick James | @VisualVincent - Yes, ScreenID last. | |
| Feb 6, 2019 at 20:06 | comment | added | Rick James | @VisualVincent - A messy query: "Show all Tuesday matinee showings of G-rated movies." The likely technique is to index some of the stuff, then work harder to filter the rest of criteria. | |
| Feb 6, 2019 at 20:06 | comment | added | Visual Vincent | Okay, I see. So if ScreenID is never searched for (i.e. WHERE ScreenID = ?) then I could simply place it last in the PK? -- Also yes, this will be limited by a UI as it is a website. These queries and "goals" represent want the company asked of of me. | |
| Feb 6, 2019 at 20:02 | comment | added | Rick James | @VisualVincent - The general advice I give: Determine the likely things that users will ask for; optimize for them. This may include providing a UI that lets them do only those things. (I have had good success with this.) | |
| Feb 6, 2019 at 19:57 | comment | added | Rick James | @VisualVincent - The order of columns in an index is important. If, for example, ScreenID is stuck in the middle of the index, but not used in the WHERE, the rest of that index is useless for that WHERE. | |
| Feb 6, 2019 at 19:51 | comment | added | Visual Vincent | 2) "If there is also a ScreenID due to two screens showing the same movie at the same time, tack it onto the PK. However, all the indexes will need rethinking." - Do you mean that all indexes would need rethinking if I add ScreenID (even if it doesn't need to be searched for, only retreived as part of a SELECT), or that they need rethinking now in their current state? -- Also thanks for the link! I'll be sure to read it through later! | |
| Feb 6, 2019 at 19:45 | comment | added | Visual Vincent | Edited question for (hopefully) better clarity. Now, I have a couple questions regarding your answer: 1) "Are there cases where the main part of the WHERE is MovieID=...?" - Do you mean when it's only checking MovieID or when it's checking MovieID first? (if the former: Not at the moment || if the latter: Also not at the moment, how exactly does it make things different?) | |
| Feb 6, 2019 at 19:29 | comment | added | Visual Vincent | Sorry for the confusion. The first three bullet points are just a description of what tables I have and what they contain (i.e. "A list of all showtimes for all movies in all cities" means the table contains all the showtimes across the entire website, but they're not output all at once - they are what's queried by the next three bullet points located under "On the site the user must be able to"). | |
| Feb 6, 2019 at 19:20 | history | edited | Rick James | CC BY-SA 4.0 | added 1175 characters in body |
| Feb 6, 2019 at 19:15 | history | edited | Rick James | CC BY-SA 4.0 | added 1175 characters in body |
| Feb 6, 2019 at 19:03 | history | answered | Rick James | CC BY-SA 4.0 |