In-between filming I added a ton of data to the database. I just created about 2000 insert scripts, we now have a little over 2000 users in the show notes.

I'll put a link to that same script if you want to paste it in mySQL workbench and create those same 2000 users. The reason why I did it is because I wanted to show you the importance of the power of using the limit command in SQL. and I'm going to say USE dev_camp_sql_course_schema and SELECT * FROM users;

Now if I do this mySQL limits it by default to 1000 and rows. If I say don't limit and I run this query it is going to go in it's going to grab all 2000 records.

Now that may not seem like a very big deal but if you had a million records in a database this could be very slow. Even with 2000 it could be very slow in a web application. If you're doing things such as grabbing users and a list of how many comments they've had, posts they've written, or whatever your application does.

This could be a very slow query. It would not really be a great user experience to list all two thousand users on the same page. What you can do is you can actually limit the records you want coming in. Here I'm going to say “LIMIT 10”. I’m going to run this code and you can see that it went and it only grabbed 10 users.

This is allowing us to grab 10 users and now we have a better user experience. So this is one way of limiting it.

Limit also has an optional argument. So if I say that I want to “LIMIT” 5 and 10. Notice here we have users_ids of 1 all the way through 11.

The reason why even though it is 10 records it's because at some point I deleted a user who had a users_id of four in the database. That's the reason why it says 11 but really there are 10 records. Now when I run this and let's remember back we have user id of one. Actually let me just make things easier for telling that there really is a difference.

I'm going to have them open in different tabs. So before I limited just to 10. Now we're limiting 5 and 10.

Now if I run the entire thing it's going to give us two data sets. The first one is our normal limit query and this went and It created the database. It said let me bring you the first 10 records in the Users table.

Nice and quick but now how would you be able to do something such as pagination? Well, that is where the second command comes in. Now if I switch over to the second tab you notice this brings in a different set of users.

This user set starts at User ID 7 and goes to 16. The 5 is actually what's called an “offset”. So if you have ever seen a feature such as pagination on a Website, where you went and you saw the first 10 posts of a blog. Then you click next on that page and then you see the next 10 posts. The way that it accomplishes that is by leveraging offsets.

The first number after LIMIT is actually the offset. We're saying I still want you to limit it to 10 but I want you to skip the first 5. This is a way that you can create that kind of pagination type of feature.

Now if I came here and made a new query and wanted to go with 15.

Now if we run all three of these you're going to see we have three tabs and now notice that the user ID starts at User ID 17.

Remember the reason why skipping a couple is because we have a users_id that's off. In all reality what this is doing is it's saying
“don't bring me the first 15 records”. It's then going to give me the users_id 16 through 26. This is a way where we can do offsets and if you're wanting to implement pagination and you need to do it manually.

This is a great way of being able to manage it. It makes it so you don't have to return the entire data set. Then you are able pick and choose the items that you want to show on a page. You can actually have SQL do the work for you. Then say if you go to the second page check to see is this the second page?

Okay, well give me the second set of results and then pass in these limit the values. This is two different ways that you can use limit in SQL. One is “limit” by itself to just limit a result set. The second is to pass an offset so you can choose what you get. You can still have your limit in place but you can actually control which records that you're going to have.

SQL Code

USE devcamp_sql_course_schema;

SELECT *
FROM users
LIMIT 10;

SELECT *
FROM users
LIMIT 5, 10;

SELECT *
FROM users
LIMIT 15, 10;

Resources

• Insert 2000 users script