mysql query optimization

Mysql query, to filter out the results of a certain field packageId (not the primary key) = num, is it better to use not in directly or to filter out when the query is completed and the result set is traversed. After the query is completed, the result set needs to be traversed no matter what.

Reply content:

Mysql query, to filter out the results of a certain field packageId (not the primary key) = num, is it better to use not in directly or to filter out when the query is completed and the result set is traversed. After the query is completed, the result set needs to be traversed no matter what.

The subject added the question in the comments

We just take the index into consideration, so we consider that query conditions like will not use the index, and the full table query speed will be very slow. However, the amount of data for this task is still very large. This is a function that sends text message reminders to some package users of the website. It is not equal to 7 because this package is given for free and is not considered. So it must be removed.

For this kind of full table traversal with a large amount of data, first of all, this method of fetching the whole table is not advisable. You can consider querying in the form of primary key id segmentation. For example, your first query id > 200 records of 0, then record the maximum id, and then query > 200 records of the current maximum id, and so on

In this scenario of id segmentation, you can safely put your packageId into the query conditions, because it must go to the primary key id, so the maximum number of data filtered by mysql is only 200, so it is not a big problem. (200 is just an example, you can control it according to the actual situation and service pressure)

An additional benefit is that you can also observe the progress of the current task in real time (if 200 users are sent, you can log it, and if it is interrupted, you can also know where to start)

---

==== Here is the original answer ===

Your query does not have paging, so it is assumed that the amount of your data is actually very small. And because there is no paging, there will inevitably be a full table scan. Just do it directly during the SQL query. There is not much difference
In addition , it’s just not equal to a value, so it’s more intuitive to use !=

If your query has a large amount of data, or it actually has paging (but your demo code here has not been written yet), then you should consider whether there are other indexed query conditions to assist your query. to your data, if not, then you need to consider whether to add an index to this packageId (if the distribution of packageId is scattered enough in this table)

select * from userPackage where packageId<>7

Use directly

The first one is faster.
Mysql query speed is much faster than traversal speed. The fewer iterations when traversing, the better.

It must be the first speed. Don’t take out all the data. . . mysql can’t stand it