Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
MySQL is by far the most popular database management system for small- to medium-sized web projects. In this course, Bill Weinman provides clear, concise tutorials that guide you through creating and maintaining a MySQL database of your own. Bill explores the basic syntax, using SQL statements to create, insert, update, and delete data from your tables. He also covers creating a new database from scratch, as well as data types, transactions, subselects, views, and stored routines. Plus, learn about the multi-platform PHP PDO interface that will help you connect your database to web applications.
Transactions are also commonly used to increase performance. If a long list of inserts or updates are performed as a transactional unit, they can perform much faster. For this example, we're going to use the scratch database. I'm going to start by creating a simple table. CREATE TABLE and we'll call it test and we'll give it an ID column and a data column. So I'll go ahead and execute that and our table has been created.
Now I'm going to insert some rows. I'm going to insert 1,000 rows but they'll all be the same. So it will be pretty easy to code this. INSERT INTO test, the data column, values, and 'this is a good sized line of text' and now I'm going to copy and paste this a thousand times and we'll just select count to make sure. I'll go ahead and execute this and you see we have a 1,000 rows and it took 543.27 milliseconds.
If I do this again a couple of times, you see the number will decrease a little bit but now it's still in the 300 milliseconds range. So this is a great little example. If I put in a start transaction here at the top and come all the way down here to the end. I'll just do that with my keyboard and say COMMIT down here at the end and press Go again. See our speed has decreased significantly here as I do it over and over again.
It's now in the 70 millisecond range. So that's a significant improvement in performance just by wrapping this 1,000 inserts in a start transaction and I'm going to press the n key here and a commit. So I'm going to go ahead and drop this table. DROP TABLE test to return our database to its original state. Transactions are commonly used to increase performance. If a long list of inserts or updates are performed as a unit, they can be performed much faster.
There are currently no FAQs about MySQL Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.