MySQL efficient SELECT query

Question

I'm not a database professional, but currently working on one query (PHP->MySQL):

I have 3 tables:

'Items': id, name, link

'ItemsToUsers': id, item_id, user_id

'Users': id, email

Each 'Item' availability is submitted to regular changes which I check on fly by some algorithm.

My goal is to

1) SELECT all Items and check on fly if they are available

2) If Item is available, notify users who are monitoring it by email. For that I need to SELECT users from 'ItemsToUsers' and then get their emails from Users table.

I know how to do it in a straightforward way, but I feel that I will fall into running to many queries. (individual SELECT for every user...)

Is there a way to do it more efficiently: in one query or by changing the algorithm? Thank you for your time!

Let's see a mock up of the SELECT(s) you need. Possibly you need only one SELECT with JOINs to connect the three tables. — Rick James, Mar 04 '15 at 20:03
Use [`JOIN`](http://dev.mysql.com/doc/refman/5.0/en/join.html), and don't forget indexes on the joined columns. — Marcus Adams, Mar 04 '15 at 20:04
Where's your "availability" column? I can only see id,name, and link for item. — MegaAppBear, Mar 04 '15 at 20:09

score 0 · Accepted Answer · answered Mar 04 '15 at 20:11

0

There's not enough information to determine how an item is available. This severely impedes the ability to query item 2.

That said, let's suppose we add a "available" column to the Items table that is a tinyint of 0 for not available, 1 for available.

A query, then, which would get all email addresses for persons watching items that are available is:

 SELECT u.email FROM Users AS u JOIN ItemsToUsers AS k ON k.user_id = u.id JOIN Items AS i on i.id = k.item_id WHERE i.available = 1;

Alternatively, you could use a subquery and IN.

Let's suppose you have a different table called Availability with the columns id, item_id and available, which again is a tinyint containing a 1 for available and 0 for not available.

SELECT u.email FROM Users AS u JOIN ItemsToUsers AS k ON k.user_id = u.id WHERE k.item_id IN (SELECT a.item_id FROM Availability AS a WHERE a.available = 1);

Again, without an idea of how you are getting a list of available products, it is impossible to optimize your queries for retrieving a list of email addresses.

answered Mar 04 '15 at 20:11

I'm not storing the availability status anywhere - I just check it on fly during foreach ($result as $row) {}. But I might consider checking for them first, then storing those which are available, and then getting emails. Thank you for query examples. – user3078775 Mar 04 '15 at 20:17
That's your bottleneck. I'd suggest changing your algorithm to query directly for available products. However, if that can't easily be done, you could create a stored procedure that accepts an array of IDs and returns all records with those IDs. See devart answer at http://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine – Mar 04 '15 at 20:22
Don't get in the habit of `IN ( SELECT ... )` -- Performance is really bad. – Rick James Mar 04 '15 at 21:27
True; subqueries are a bad idea as a rule. But vs. running hundreds of queries, they can be the better option. – Mar 05 '15 at 17:53

score 0 · Answer 2 · edited May 23 '17 at 11:47

Your steps allude to doing this in n+1 queries (where n = number of entries in the Items table):

SELECT * FROM Items;    -- This is the +1 part

While iterating over that result set, you intend to determine if it's available and, if it is, to notify users who are watching it. Assuming you have a given item id and you want to select all users' email if that product id is active, then you could do this:

SELECT email FROM Users u 
    INNER JOIN ItemsToUsers iu ON iu.user_id = u.id
    INNER JOIN Items i ON iu.item_id = i.id
WHERE i.id = {your item id}

You would be running this query for every item in your table. This is the n part.

In general you could instead generate a list of emails for all users who are watching all products that are active, after you have already determined which ones should be active:

SELECT DISTINCT email FROM Users u 
    INNER JOIN ItemsToUsers iu ON iu.user_id = u.id
    INNER JOIN Items i ON iu.item_id = i.id
WHERE i.is_active = 1

This will get the job done in a total of 2 queries, regardless of how many users or items you have. As a bonus, this one can give you distinct emails, whereas the first solution would still need application-level code to remove duplicates returned by the multiple queries.

yeah, you understood my concern with running too many queries - I really like your solution — user3078775, Mar 04 '15 at 20:21
I just read your comment that you don't have that availability flag actually stored for the items, so if you want to use this solution you would need that to be able to query against. Hope this helps! — Jeff Lambert, Mar 04 '15 at 20:23

score 0 · Answer 3 · answered Mar 04 '15 at 20:21

0

SELECT  Items.id, Items.name, Items.link FROM Items
INNER JOIN ItemsToUsers ON ItemsToUsers.item_id = Items.items.id
INNER JOIN Users  ON ItemsToUsers.user_id = Users.id ;

answered Mar 04 '15 at 20:21

mysqlrockstar

2,536
1
19
36

MySQL efficient SELECT query

3 Answers3