selecting random values from multiple ranges

[f3ze]

Hi, Any help with this would be appreciated...

using mysql 4.1.22-standard-log

 

I have 2 tables, one with business info, one with images.

I would like 2 businesses from the first table, each from a different range of values

Then the image info for those 2 businesses from the images table

 

This is what I have so far:

(

SELECT table_businesses.id, table_businesses.column_wanted
FROM  table_businesses
LEFT JOIN table_images
ON table_images.id = table_businesses.id 

WHERE  table_businesses.column_2 >= '1' 
AND table_businesses.column_3 = '1' 
AND table_images.column_4 != '1'
AND table_businesses.biz = 'bank'

ORDER BY RAND()
LIMIT 1

)

UNION

(

SELECT table_businesses.id, table_businesses.column_wanted
FROM  table_businesses
LEFT JOIN table_images
ON table_images.id = table_businesses.id 

WHERE  table_businesses.column_2 >= '1' 
AND table_businesses.column_3 = '1' 
AND table_images.column_4 != '1'
AND table_businesses.biz = 'farm'

ORDER BY RAND()
LIMIT 1

)

 

This works but it's slow (takes around 0.8s, with 400 records in the images table and 140 in the businesses table).

The table_a.id is the primary index, and on the images table there is an index (table_images.id,table_images.column_4)

 

Using explain, it is using a good index for the businesses but images gives 'null' and 'Using where; Using temporary; Using filesort' and goes through all rows.

 

Thanks for any help.

 

 

[fenway]

You're using != , so no index... inner join is probably what you want vs. left, and order by rand() is poor as well.

[f3ze]

Thanks for the suggestions, down to 0.03secs now, but still looks like it could be improved.

 

I've made a column in the table which is sorted randomly but with one group after another (display_order_bizheader).

After reading http://www.phpfreaks.com/forums/index.php/topic,125759.msg522853.html#msg522853 I came up with this complex looking code. Basically the idea is to find the difference between the highest and lowest record number of each grouping, multiply it by a random number, then add that to the lowest id number. I reckon this can be done simpler, looking forward to enlightenment!

 

(
SELECT advertisers.advertiser_id, advertisers.thebizname, advertisers.bizname, advertisers.detail, advertisers.bizheader, advertisers.side_tag, advertisers.location, advertisers.description, images.image_caption, images.image_logo, images.image_filename, images.date 
FROM advertisers 
INNER JOIN images ON images.advertiser_id = advertisers.advertiser_id 
WHERE display_order_bizheader 
= (SELECT MIN( display_order_bizheader ) FROM advertisers WHERE bizheader = 'accommodation' ) 
	+ FLOOR( 
	RAND( ) * 
			(
				( SELECT MAX( display_order_bizheader ) FROM advertisers WHERE bizheader = 'accommodation' ) 
				- ( SELECT MIN( display_order_bizheader ) FROM advertisers WHERE bizheader = 'accommodation' )
			) 
		)

AND images.image_logo = '0' LIMIT 1 
)
UNION 
(
SELECT advertisers.advertiser_id, advertisers.thebizname, advertisers.bizname, advertisers.detail, advertisers.bizheader, advertisers.side_tag, advertisers.location, advertisers.description, images.image_caption, images.image_logo, images.image_filename, images.date 
FROM advertisers 
INNER JOIN images ON images.advertiser_id = advertisers.advertiser_id 
WHERE display_order_bizheader 
= (SELECT MIN( display_order_bizheader ) FROM advertisers WHERE bizheader = 'activities' ) 
	+ FLOOR( 
	RAND( ) * 
			(
				( SELECT MAX( display_order_bizheader ) FROM advertisers WHERE bizheader = 'activities' ) 
				- ( SELECT MIN( display_order_bizheader ) FROM advertisers WHERE bizheader = 'activities' )
			) 
		)

AND images.image_logo = '0' LIMIT 1 
)

[f3ze]

attached a screenshot of the explain output if that helps, thanks!

 

[attachment deleted by admin]

[fenway]

I don't really see how that could be much better... not sure I understand that new column you made.

[f3ze]

Thanks for the suggestions by the way. Yep the new column is basically to implement the alternative rand() method that you posted. If I just took a random result from the main key it could return results from the same sub-group, but the new column is numbered entries (eg 1,2,3,4) continuing through groups. eg if the two groups i'm after are records (1,2,3,4,5) and (23,24,25), it can then do a MIN + (RAND() * (MAX-MIN)) to get a random number from the new column. (eg. for the first group: 1 + rand*(5-1) ).

 

Seems a bit convoluted, and the key for some reason is going through 250 rows. (only about half) The images table should be a quick one because by the time it searches that it should have an id to find, and then just needs to check that one column = 0, so not really sure why it needs to search 250 rows.

 

Anyway I guess its about 3x faster than before so that'll do until the tables are bigger

[f3ze]

The previous code I posted seems to occasionally return 1 result instead of 2. There's easily enough business with the only limiter

AND images.image_logo = '0' LIMIT 1

that it shouldn't do that.

Now, if I remove the limiter (that line) it always returns an empty result, whereas the way I'm looking at it it should return a result each time. I don't get why including the 'and' statement makes it return a result when it's a limiter?

 

Basically I would like to find a random business, then find it's image details. Each business has multiple images, but one images result will be a logo, which I don't want. Maybe there's a way of limiting the search of the images table by this

image_logo = 0

before it has chosen a random business?

 

Sorry for the complete lack of knowledge on my part, any help appreciated!

[f3ze]

okay I answered one question, i no longer receive empty results when I remove the 'image_logo = 1' line, by putting the left join back to an inner join. But it still randomly returns a single result instead of 2...

[fenway]

I've no idea what you've done... but you have a union...

[f3ze]

Ah, I was wondering about that... Is there a better way than using Union?

I've since modified the way of looking for the results by finding a random number with a separate query.  Then I would like to get the results from two tables for the 2 different IDs.

The explain shows that it looks through half the images table to find the row, but surely it could just go straight to the ID?

Should this be another Union so that I find everything from the main table first then hit up the images table where ID = x ?

Think this may be confusing, sorry!

At the moment it is doing the same query twice, just with a different id.

Thanks for the help fenway

 

(
SELECT 
advertisers.advertiser_id, thebizname,
images.image_caption, image_logo 
FROM advertisers
RIGHT JOIN images
USING ( advertiser_id ) 
WHERE advertisers.display_order_bizheader =  '79'
AND images.image_logo =  '0'
LIMIT 1
)
UNION 
(
SELECT 
advertisers.advertiser_id, thebizname,
images.image_caption, image_logo 
FROM advertisers
RIGHT JOIN images
USING ( advertiser_id ) 
WHERE advertisers.display_order_bizheader =  '135'
AND images.image_logo =  '0'
LIMIT 1
)

[fenway]

So if I understand correctly, you want one image per advertiser? In this case, you will always get 1 row each, for a total of 2 rows.  And never use right join...

[f3ze]

Ah thanks, oh not sure how the right join got in there!

Thanks for your help.

[fenway]

Ah thanks, oh not sure how the right join got in there!

Thanks for your help.

Did I help?