MySQL Limits

September 29th, 2009, 9:31 pm

Does anyone know how I can do this in Mysql?

Select a limit of 4 photos, from a list of users

EG: I have users bob, jason and sue. They have uploaded about 20 photos each. But I only want 4 photos from each of them.

SELECT photoid FROM galleries WHERE user=bob OR user=jason or user=sue

This will give me ALL of their photos.

SELECT photoid FROM galleries WHERE user=bob OR user=jason or user=sue LIMIT 4

This will give me 4 photos from bob but nothing from jason or sue.

How do I get 12 photos, 4 from bob, 4 from jason, and 4 from sue? Must I use multiple MySQL statements?

[Bigwebmaster]

September 29th, 2009, 10:40 pm

If it were me I would probably use a statement like this:

MYSQL Code: [ Download ] [ Select ]

SELECT photoid, user
FROM galleries
WHERE user IN ('bob', 'jason', 'sue')
ORDER BY photoid DESC

1. SELECT photoid, user
2. FROM galleries
3. WHERE user IN ('bob', 'jason', 'sue')
4. ORDER BY photoid DESC

I am making the assumption here that the higher the photoid the newer the photo is. Then after that I would use whatever scripting language you are using to limit it to 4 results per user. For example:

PHP Code: [ Download ] [ Select ]

$photoArray = array();
while($row = mysql_fetch_row($result))
{
   if(count($photoArray[$row['user']]) < 4)
   {
      $photoArray[$row['user']][] = $row['photoid'];
   }
}

1. $photoArray = array();
2. while($row = mysql_fetch_row($result))
3. {
4.    if(count($photoArray[$row['user']]) < 4)
5.    {
6.       $photoArray[$row['user']][] = $row['photoid'];
7.    }
8. }

That would only add photoids to the specified user's array as long as they have less than 4 photos. I haven't tested this, but it would be something along these lines. In the end you would have a multidimensional array of users with their latest photo ids that you could do whatever you need with.

September 29th, 2009, 11:06 pm

That would work. But I was thinking of a specific Mysql statement that would just get 4 images from each user...

Hmm

[joebert]

September 29th, 2009, 11:08 pm

Here's what I did.

First my test table.

SQL Code: [ Download ] [ Select ]

--
-- Table structure for table `gallery`
--
 
CREATE TABLE `gallery` (
  `user` int(11) NOT NULL,
  `photo` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`photo`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 

1. --
2. -- Table structure for table `gallery`
3. --
4.  
5. CREATE TABLE `gallery` (
6.   `user` int(11) NOT NULL,
7.   `photo` int(11) NOT NULL AUTO_INCREMENT,
8.   PRIMARY KEY  (`photo`)
9. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
10.  

I inserted 12 rows for users 1, 2, and 3 manually real quick.

I tried this monster.

SQL Code: [ Download ] [ Select ]

SELECT
   a.user,
   a.photo AS aa,
   b.photo AS bb,
   c.photo AS cc,
   d.photo AS dd
FROM gallery a
   LEFT JOIN gallery b
      ON a.user = b.user
         AND a.photo <> b.photo
   LEFT JOIN gallery c
      ON b.user = c.user
         AND a.photo <> b.photo
         AND a.photo <> c.photo
         AND b.photo <> c.photo
   LEFT JOIN gallery d
      ON c.user = d.user
         AND a.photo <> b.photo
         AND a.photo <> c.photo
         AND a.photo <> d.photo
         AND b.photo <> c.photo
         AND b.photo <> d.photo
         AND c.photo <> d.photo
GROUP BY user

1. SELECT
2.    a.user,
3.    a.photo AS aa,
4.    b.photo AS bb,
5.    c.photo AS cc,
6.    d.photo AS dd
7. FROM gallery a
8.    LEFT JOIN gallery b
9.       ON a.user = b.user
10.          AND a.photo <> b.photo
11.    LEFT JOIN gallery c
12.       ON b.user = c.user
13.          AND a.photo <> b.photo
14.          AND a.photo <> c.photo
15.          AND b.photo <> c.photo
16.    LEFT JOIN gallery d
17.       ON c.user = d.user
18.          AND a.photo <> b.photo
19.          AND a.photo <> c.photo
20.          AND a.photo <> d.photo
21.          AND b.photo <> c.photo
22.          AND b.photo <> d.photo
23.          AND c.photo <> d.photo
24. GROUP BY user

Got a row for each of the 3 users with userid and 4 unique photos that belonged to them.

Then I added an index to user.

SQL Code: [ Download ] [ Select ]

--
-- Table structure for table `gallery`
--
 
CREATE TABLE `gallery` (
  `user` int(11) NOT NULL,
  `photo` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`photo`),
  KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 

1. --
2. -- Table structure for table `gallery`
3. --
4.  
5. CREATE TABLE `gallery` (
6.   `user` int(11) NOT NULL,
7.   `photo` int(11) NOT NULL AUTO_INCREMENT,
8.   PRIMARY KEY  (`photo`),
9.   KEY `user` (`user`)
10. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
11.  

Then I INSERT-SELECT-ed the table until it had 368K rows with unique photo columns.

Then I assigned those to roughly 100K random users.

SQL Code: [ Download ] [ Select ]

UPDATE gallery SET user = ROUND(RAND() * 100000)

Then I ran the monster from before, with the addition of a WHERE clause

SQL Code: [ Download ] [ Select ]

SELECT
   a.user,
   a.photo AS aa,
   b.photo AS bb,
   c.photo AS cc,
   d.photo AS dd
FROM gallery a
   LEFT JOIN gallery b
      ON a.user = b.user
         AND a.photo <> b.photo
   LEFT JOIN gallery c
      ON b.user = c.user
         AND a.photo <> b.photo
         AND a.photo <> c.photo
         AND b.photo <> c.photo
   LEFT JOIN gallery d
      ON c.user = d.user
         AND a.photo <> b.photo
         AND a.photo <> c.photo
         AND a.photo <> d.photo
         AND b.photo <> c.photo
         AND b.photo <> d.photo
         AND c.photo <> d.photo
WHERE a.user IN (100, 500, 10000, 25000)
GROUP BY user

1. SELECT
2.    a.user,
3.    a.photo AS aa,
4.    b.photo AS bb,
5.    c.photo AS cc,
6.    d.photo AS dd
7. FROM gallery a
8.    LEFT JOIN gallery b
9.       ON a.user = b.user
10.          AND a.photo <> b.photo
11.    LEFT JOIN gallery c
12.       ON b.user = c.user
13.          AND a.photo <> b.photo
14.          AND a.photo <> c.photo
15.          AND b.photo <> c.photo
16.    LEFT JOIN gallery d
17.       ON c.user = d.user
18.          AND a.photo <> b.photo
19.          AND a.photo <> c.photo
20.          AND a.photo <> d.photo
21.          AND b.photo <> c.photo
22.          AND b.photo <> d.photo
23.          AND c.photo <> d.photo
24. WHERE a.user IN (100, 500, 10000, 25000)
25. GROUP BY user

It returned results in a fraction of a second on a fairly old computer. I'm assuming it's accurate because my initial test with no difference other than that WHERE clause was accurate.

There happened to be only 3 photos for user 500, so the 4th column was NULL.

[Bigwebmaster]

September 29th, 2009, 11:18 pm

camperjohn wrote:

That would work. But I was thinking of a specific Mysql statement that would just get 4 images from each user...

Hmm

As far as I am aware there would be no such simple statement. You would have to do something like Joebert did above. The problem with Joebert's solution is that if down the road you want to change it to show 8, 10, or 20 photos from users that MySQL statement will get much more complicated. Sometimes it makes sense to use simplier MySQL statements with some coding to help. That is what I would do unless your MySQL statement is returning potentially millions of results, that PHP loop could be quite time consuming then and it may make more sense to limit the results returned like Joebert's example does.

September 29th, 2009, 11:25 pm

Yeah I think I will just make a foreach loop and not let MySQL do it.

When I have to select 8 photos, from 2500 users, the MySQL statement will be horrendous.

[joebert]

September 30th, 2009, 9:03 am

I'm interested in finding out what would be more efficient on a live site,

1) multiple queries
2) a function that generates that monster query

I actually had this problem on the back of my mind a couple of days ago as I want to pull a configurable number of columns for a variable number of categories when I get to doing the site/category index page stuff.

[Bigwebmaster]

September 30th, 2009, 9:18 am

In my experience monster queries are usually not as efficient as two more efficient queries. However, it really depends on how many results are being returned, how you end up processing the data, and if the monster query creates things like this:

Quote:

Using temporary; Using filesort

When using temporary tables it locks the entire table (if using MyISAM engine) and if you have alot of requests being made things can get backed up very quickly if that monster query isn't finishing fast and returning alot of results. That could eventually bring down the entire server if you have alot of traffic. That is one reason I prefer to have two quicker queries and put the load on PHP to help out. Also another thing to note is that some temporary tables are created in memory (fast), while others that are too large are created on disk (very slow). So if you have a query that is using temporary tables created on disk, the table could be locked for a long time which means everything trying to access that table will be on hold until it is finished. Not good.

Temporary tables aren't always a horrible thing, just if the query takes too long to complete. Sometimes temporary tables are necessary and are usually okay with very efficient queries. Here is a fairly good article:

http://www.sql-server-performance.com/a ... es_p1.aspx

I would be curious what an explain on your query would return Joebert.

[joebert]

September 30th, 2009, 9:55 am

I'm thinking about it now, and I think the only reason that monster query did so well with 300K+ rows, is that the where clause and the index on the user column greatly reduced the number of columns grabbed for the joins.

My test table had 300K rows, but the max number of photos associated with any user was 14.

SQL Code: [ Download ] [ Select ]

SELECT COUNT( photo ) AS total
FROM `gallery`
GROUP BY user
ORDER BY total DESC
LIMIT 1

1. SELECT COUNT( photo ) AS total
2. FROM `gallery`
3. GROUP BY user
4. ORDER BY total DESC
5. LIMIT 1

So I INSERT-SELECT-ed the table size to 1.5M rows, then I reduced the number of user ids to 25K.

That same monster query looks like it's going to take several minutes to return anything with this new table. I just restarted the MySQL server so I could find out the max number of rows any user has is 94.

[Bigwebmaster]

September 30th, 2009, 10:03 am

Also when you get a chance post your explain results for that monster query:

SQL Code: [ Download ] [ Select ]

EXPLAIN SELECT
   a.user,
   a.photo AS aa,
   b.photo AS bb,
   c.photo AS cc,
   d.photo AS dd,
...
 

1. EXPLAIN SELECT
2.    a.user,
3.    a.photo AS aa,
4.    b.photo AS bb,
5.    c.photo AS cc,
6.    d.photo AS dd,
7. ...
8.  

[joebert]

September 30th, 2009, 10:26 am

This is from a copy of the gallery table with 110 rows in it and 21 users.
That monster query is really going to take awhile with 1.5M rows.

Code: [ Download ] [ Select ]

+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref     | rows | Extra    |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE   | a   | range | user     | user | 4    | NULL    |  18 | Using where |
| 1 | SIMPLE   | b   | ref  | user     | user | 4    | test.a.user |  12 |       |
| 1 | SIMPLE   | c   | ref  | user     | user | 4    | test.b.user |  12 |       |
| 1 | SIMPLE   | d   | ref  | user     | user | 4    | test.c.user |  12 |       |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
4 rows in set (0.00 sec)

1. +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
2. | id | select_type | table | type | possible_keys | key | key_len | ref     | rows | Extra    |
3. +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
4. | 1 | SIMPLE   | a   | range | user     | user | 4    | NULL    |  18 | Using where |
5. | 1 | SIMPLE   | b   | ref  | user     | user | 4    | test.a.user |  12 |       |
6. | 1 | SIMPLE   | c   | ref  | user     | user | 4    | test.b.user |  12 |       |
7. | 1 | SIMPLE   | d   | ref  | user     | user | 4    | test.c.user |  12 |       |
8. +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
9. 4 rows in set (0.00 sec)

[Bigwebmaster]

September 30th, 2009, 10:50 am

Wow so that small table with 21 users require you to still search 31,104 rows. You get that by multiplying all the rows together on that explain. So 18x12x12x12 = 31,104. I wonder how many it would search if you have 10000 users.

[joebert]

September 30th, 2009, 11:20 am

Another case where any way you look at it, a one-liner just isn't an option. It might work well for several users who have only a few photos, but when do you ever see that ?

September 30th, 2009, 2:48 pm

My table size is about 40,000 galleries, 100,000+ images and videos, and about 35,000 members. Many members have 0 photos.

October 8th, 2009, 7:34 am

Thanx for sharing Knowledge its use full for me