Slow queries can be a serious problem in MySQL. Learn how diagnose problems with broken or slow queries using the MySQL Workbench EXPLAIN functionality. Find out how indexes can dramatically speed up data operations.
- [Instructor] We have a technically correct query…to find the average accident severity,…but it's very slow.…Slow queries are a common problem in MySQL,…but fortunately there's a way of exploring queries…that seem to be taking a long time.…I'm going to stop this query…and then click the Explain button in the toolbar.…Now, there's a lot to see here,…but first notice that MySQL is going to be performing…full table scans of all three tables involved in the query.…For the tiny vehicle type table,…that's probably no big deal,…but for the other tables,…the query will have to parse through…hundreds of thousands of rows each.…
And if I change the display info…just to show the data read per join,…you can see that the query will have to go through…a total of 841 million rows.…No wonder it's taking so long.…Fortunately there's a way to make things faster…using table indexes.…When MySQL is asked to select data from indexed columns,…it can use a technique called binary tree partitioning…to subdivide the data…and then minimize the number of hops it has to go through…
- Strengths and weaknesses of MySQL
- Creating a database
- Joining data sets
- Integrating Python with MySQL
- Searching a database
- CRUD operations
- Performing calculations
Skill Level Appropriate for all
1. Data and MySQL in under Five Minutes
2. Create a Database
3. Join Two Datasets
4. Search a Database
Searching a database1m 51s
5. Create, Read, Update, and Delete Operations
6. Averages and Calculations
Next steps1m 1s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.