MYSQL :: Optimize Rand() On A Big Database

Rand()?
I use this query to return the three fields of all providers in my database.

PHP

$result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY ID ASC LIMIT $from, $max_results ");

I'd like to instead return a random list of the providers, how do I do that?
(like this?)

PHP

$result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY rand(ID) LIMIT $from, $max_results ");

FIRST() And RAND()
I have two tables:

Sport (Id, Name)
SportPhoto (Id, SportId, Active) (the actual photos are stored elsewhere...)

I want a VIEW and/or PROCEDURE that will get a the list of sports with a random photo for each. I need something like this:

SELECT FIRST(Id ORDER BY RAND()) Id, SportId
FROM SportPhoto
WHERE ACTIVE = 1
GROUP BY SportId;

where that would get the list of sports with one random photo for each sport. I guess there's no FIRST() aggregate function in MySQL. So, how would I do what I need to do?

Order By If(rand()....)
I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need.

SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering
FROM tProducts p
JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1
JOIN taxCodes tax ON p.taxCode=tax.taxCode
WHERE p.featuredProduct=1
AND p.stocklevel>-3
GROUP BY p.productID
ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand()
LIMIT 10

RAND Function
Basically, I have a stored function that is designed to deliver a random 3 digit code. But I need it to be of the format #~#~# (ie, 3~0~7, 5~1~5, etc) So I set up the function to generate 3 random digits and then concatenate them together, using the following lines inside the function:

SET v1 = FLOOR(RAND() * 10);
SET v2 = FLOOR(RAND() * 10);
SET v3 = FLOOR(RAND() * 10);
SET vCode = CONCAT_WS('~', v1, v2, v3);

The function works fine, and gives me back my 3 digit code in the exact format that I want.

However, after testing it out, I've discovered that it isn't entirely random. There are certain combinations that I never end up getting returned from the function. There are basically 1000 different possible combinations, but I'm only ever receiving 800 combinations back. I've loop tested the function to generate literally millions and millions of different codes, and there are certain codes that I just never see. Code:

Using Rand() Within Categories
Supposed the database looks like this: image_id, category_id, description, and for each category_id a, b, c, d, e, and f (6 categories), there are 5 images (5 image_ids). And i would like to select randomly one of the 5 images from each category. the result of the query should have 6 rows with images with the different categories, eg:

image_id / category_id / description
2 / a / test
7 / b / test
12 / c / test
29 / d / test
53 / e / test
77 / f / test

is it possible to do this?

ORDER BY RAND()
RedTable(name TEXT,newness INT)

I want to select random records but at the same time select those with the highest 'newness' value. So if the highest newness value in the table is 5 and ten records have that value I want to select a random one from those ten, but if all the records have the same 'newness' I want a completely random one.

Can I do this: "SELECT name FROM RedTable ORDER BY newness,RAND() LIMIT 1"

I don't know if that will really work. It's important that I maintain randomness so I really need to be sure.

Rand() Not Working In Php?
I'm trying to get the rand from the mysql. When I use the MySQL Front software (SELECT * FROM keyword ORDER BY RAND() LIMIT 2, it gives me random which is working fine.

But when I use the php and it doesn't give me random.
What wrong with php?

$query = "SELECT * FROM keyword ORDER BY rand() LIMIT 2";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

echo "<PRE>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print_r($line);
}
echo "</pre>";
mysql_free_result($result);

Rand() Question
hey guys i need mutiple images on one page in random use.
i have this

$QQQ1 = mysql_query("SELECT
*
FROM
supps AS t1
INNER JOIN
(
SELECT
ROUND(RAND() * (SELECT MAX(id) FROM supps)) AS id
) AS t2
ON
t1.id >= t2.id
ORDER BY
t1.id ASC
LIMIT 1; ");

RAND() With Weighted Probabilities
I don't really think this is possible, but I've had a few other posts where I've said just that, and have been pleasantly surprised in the end...

I'll be running some ads on the side of my site in an attempt to earn nominal amounts of money--ads in the plural, referring to multiple sources (e.g. Google AdSense, AdBrite, and a couple others). However, I will only be displaying ads from one source at a time. This is done rather easily, by storing the Ads HTML in a database which I've named advertisements, selecting them like this:
SELECT ad_html FROM advertisements ORDER BY RAND() LIMIT 1However, when I do that, each ad has essentially the same probability of appearing as the next. For instance, if I have a total of 5 ads saved in the database, each one has a 1/5-chance of appearing. I would like to give "weights" to each of the ads, so to speak, meaning that particular ads should show up more often than others.

The easy way to do this, of course, is to just store duplicate rows in the database, thereby naturally increasing the chances of certain ads being selected. But that seems messy, both in theory, and because it takes up more space. I'm wondering if there's any way I can add another column to my advertisements table, call it "ad_weight" (or something like that), that would be a real valued number from 0 to 1, indicating the probability that the particular row it describes will be selected.

I have absolutely no idea how to do this, nor do I know if this is really possible, so any sort of help is much appreciated! A pure SQL-query solution is definitely preferred (if it exists), although I'd be willing to do something equivalent in PHP, if that's what it takes.

Trying To Understand RAND() And LIMIT
I have been doing some queries using a combination of factors that include RAND() and LIMIT.

My code looks like this:

ORDER BY RAND() Same Every Time
I'm trying to get a random record from a table, but the overwhelmingly recommended method - "SELECT * FROM table ORDER BY RAND() LIMIT 1" - gives me the same record every time. When I try the query without the LIMIT clause, I can see that the records are indeed in an order that cannot be explained as anything but random (i.e. not the order I entered them, and not the order of the primary key), but when I run the query again they repeatedly show up in the same "random" order. Is there something I need to do to seed it so that my results change? I see no mention of this anywhere - everyone seems to act as if this query will simply work.

Algortihm Used In Order By Rand()
Does anyone know where I can find any documentation and/or information
about the algorithm used in ORDER BY RAND() in mysql ?

Group By And Order By Rand()
I have the following data in a table:

| art_id | artist_id | category_id | title |
| 1 | 1 | 1 |A Golden Day For the Guardians of the Earth|
| 3 | 1 | 1 |Blizzard Along Bear Tooth Range|
| 9 | 1 | 2 |Spring of the Rainbow Warriors|
| 8 | 2 | 5 |Evening at Rock Island|
| 5 | 2 | 2 |Snowy March Evening|
| 2 | 2 | 7 |Afternoon Missouri River|
| 4 | 3 | 1 |Pelican King|
| 6 | 4 | 7 |Moose Cabin|

I am trying to select a random art_id , but I only want 1 id per category. I tried:

select art_id,artist_id,category_id, title from art group by category_id order by rand();

but it always gives the same art_ids, just in a different order, so I always get art_id 1 for category 1, never id 3 or 4.

Group By / Rand Problem
I try this sql, but it don't work :(
Have you a solution ?

----------------------------------------------------------------------
SELECT
galleries.id_gallerie as lid ,
(
SELECT
id_contenu
FROM
contenus
WHERE
id_gallerie=lid
ORDER BY
RAND()
LIMIT 0,1
)
as lid_contenu
FROM
galleries
WHERE
galleries.home=1 AND
galleries.online=1
------------------------------------------------------------------------------

Order By Rand() Problem
this query works fine in phpmyadmin:

SELECT name, id
FROM `raffaello_ties`
ORDER BY rand( )
LIMIT 0 , 30

when I try to do the same in php, I get an error, although I don't know why. here's the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by rand() limit 0, 30' at line 1.

Faster RAND() Query
I need to select from my db table ONE (1) random row, just one. So, I am doing this:

SELECT categories.cat_name, subcategories.sub_name, map.faq
FROM categories, subcategories, map
WHERE categories.cat_id=map.cat AND subcategories.sub_id=map.sub
ORDER BY RAND() LIMIT 1"

categories=10 rows
subcategories=20 rows
map=100 rows

What Does OPTIMIZE Do
I use phpMyAdmin and constantly see the OPTIMIZE TABLE option on the structure page with a number of bytes highlighted. You press the button and the bytes are gone! Great!!!

But what exactly did that just delete? Should you optimize or just leave the databases as they are? Is there a chance of corrupting the data in a table when you run this?

Optimize This Sql
optimize this sql

SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 .

Optimize
the following query takes forever on 80,000 records. I was wondering if there was anyone that sees how I could speed this query up.
SELECT c.products_model AS model, SUBSTRING(warehouse_sku FROM 4) AS sku, a.products_id AS products_id, products_viewed
FROM products_description AS a, products AS b, products_to_warehouses AS c
WHERE a.products_id = b.products_id
AND b.products_model=c.products_model
AND warehouse_id = 2
ORDER BY products_viewed DESC;

Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql.

i supposse itÂ´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine

Shorten/optimize This?
I have 3 fields I want to search:

categories table's Title field.
items table's Title and Info fields.

The titles are < 25 chars, and Info < 50 characters.

So, I figured a LIKE '%SEARCH%' is best... is there something else I should do?

Right now I split up each word in the search, so a search for "multiple word search" gives me these two queries:

SELECT Id, Title FROM categories WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%'

SELECT Id, Title, Info FROM items WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%' OR Info LIKE '%multiple%' OR Info LIKE '%word%' OR Info LIKE '%search%'

How Can I Optimize This Query?
tables:
---------
branch:
PK = branch_ID
(has many)
galleries:
PK = gallery_ID
FK = branch_ID
(has many)
photos:
PK = pic_ID
FK = gallery_ID

query:

select x,y,z
from galleries join photos
on photos.gallery_ID = galleries.gallery_ID
where galleries.branch_ID = 1
order by photos.img_weekHits desc,photos.img_hits desc
limit 0,1;
explain says the following:
---------
table: galleries
type: simple
possible keys:3
ref: const
rows: 200
extra: Using where; Using temporary; Using filesort
---------
type: simple
table: photos
ref: galleries.gallery_ID
rows: 35
extra: Using where

I need to run this query for each branch of my website (currently there are 7). the query gets the most popular picture for the week from that branch.

All fields used in where, joins and order by are indexed.

The photos table has 40 000+ rows in it.
The galleries table has 500+ rows in it.

The query takes about 3-500ms on my laptop to run for my busiest branch, and less for less busy branches.

The page that runs the query is cached, so the query is only run every half hour or when new content is added or the cache is flushed.

Is there any better way i can run this query, or alternatively achieve the same result with putting less load on the server?

Can I Optimize This Process?
I have a script that reads data records from MySQL and creates a table of all the records and their subsequent data for viewing. Each page of the script will only show 10 records, but with over 10,000 records, there are many many pages of records... thus I have pagination links at the bottom.

Now for the pagination to work correctly, I have to count the total number of records in the specified query, but to display only the 10 records per page, I have to run the same query a second time and add the LIMIT clause with start and display limit parameters. So right now I am calling the same query essentially twice, only the second time I specify the LIMIT clause. These queries are rather huge with multiple joins and many many columns of data.

Is there any way I can optimize this process and get the total record count of the specified query for pagination purposes, but also be able to limit the start and display count for each page as aforementioned. Or is this something I just have to use 2 seperate queries for?

Optimize Mysql
how to optimize my mysql server. I'm using DELL 2850 with 3Gb cpu and 2Gb RAM, Redhat Enterprise 3 and MySQL 4.1. Every 30-50 minutes mysql will be busy & very slow. Code:

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.

So my questions are:

(a) why does this happen?
and

(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Deletion And Optimize
I have a summary table which gets updated every half hour. Each half hour I delete the previous record for that day and the new records are summed up at the end of each half hour and gets inserted. This table has no primary/unique keys but only indexes.

After the insertion I do a optimize. This generally takes some time to optimize. Is it good idea to optimize the table after each deletion. I would like like improve the performance of my servers and also the query retrival time.

Optimize Query
What indexes are required to speed up the following query.

SELECT COUNT(*) AS cnt FROM company WHERE state = 'Maharashtra'
AND city = 'MUMBAI' AND companyName REGEXP '^D' GROUP BY parentid;

Optimize Search
If you want to retrieve data in a 10000 rows-table w/ simple WHERE clause, eg: select this where col1=val1 and col2=val2, this'll take forever (I would think, I never worked a database with such capacity.), 'cause all rows are accessed sub-sequentialy.
Are there ways to optimize this search? I read in some articles one way to do it is by indexing, but I don't quite understand how this indexing works,

Optimize Table
Optimize table in mysql is nice!! but seems to require table names.
Any way I can run it for my entire db? I like to do this in a batch
process from time to time.

FYI, I was having SERIOUS perf. problems on my box, until I optimized
the tables that had huge number of rows deleted. Don’t believe the
mysql documentation that this is not needed.

OPTIMIZE TABLE
Is it safe if the 'OPTIMIZE TALBE [table_name]' query is issued frequently(every 30 second)?

The MySQL manual,[13.5.2.5. OPTIMIZE TABLE Syntax], says,
"In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables."

Optimize Query
I'd like to optimize this query:Code:

SELECT *
FROM `links`
WHERE active = "1" AND mainweight != 0 ORDER BY Rand()*(1/mainweight) LIMIT 5

I have a database of links wich has 3 000 rows. I'd like to select weighted random links from it (mainweight is the weight of the link). On my serwer this query is executing 0.8 sec. When i have 50-100 users on my site this is a problem.

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.
So my questions are:
(a) why does this happen?
and
(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Optimize - NULL V.s. 0 Or ''
To optimize a fairly large table with variable length fields, is it generally better to use a default value of NULL or an empty string?

ex: for char types, use NULL in stead of ''
or for numeric types, use NULL in stead of 0

if there was a notable advantage/disadvantage to a lot of NULL values in a large table, and if so whether it is due to disk storage requirements or processing time/overhead?

Optimize Question
if the following is the best way to construct a table for searching x, y, and z coordinates
(especially for ranged search retrievals <, >, <=, >=, etc)
Code:

create table map (
x smallint unsigned not null,
y smallint unsigned not null,
z tinyint unsigned not null,
t smallint unsigned not null,
primary key(x, y, z)
)

The t column is simply the data at the xyz coordinate

Optimize SUM() Function With JOIN
SELECT DISTINCTROW marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport, marketing.notes AS FirstOfnotes, Sum( bookings.amtPaid ) AS SumOfamtPaid
FROM marketing
INNER JOIN bookings ON marketing.ID = bookings.marketingID
GROUP BY marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport;
EXPLAIN shows:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE bookings ALL NULL NULL NULL NULL 67289 Using temporary; Using filesort
1 SIMPLE marketing eq_ref PRIMARY PRIMARY 4 plt.bookings.marketingID 1
The index is working fine on marketing.ID (the primary key) however i cant find an index which works on the bookings table.
Ive tried adding a key on bookings.marketingID however it shows it up as a possible key but doesnt use it.
Ive also tried creating a composite key of bookings.amtPaid and bookings.marketingID, it uses this key however the number of rows it has to look through stays the same.

I cant see what else I can do to optimize this query.
It takes a while to open as for every marketing.ID it has to work out the SUM of the booking.amtPaid.

Precedence In WHERE Clause - Trying To Optimize
How can I optimize a query like:
SELECT COUNT(*)
FROM ((table1 o
INNER JOIN table2 j ON o.ID = j.ID)
INNER JOIN table3 f ON j.f_ID = f.f_ID)
INNER JOIN table4 c ON c.f_ID = f.f_ID
WHERE j.end = &#55612;&#57200;-00-00'
AND c.dateC > j.startDate
AND c.sub = 'Y'
AND f.l_ID = '$lk_ID'
AND c.show = 'Y'
AND c.dateC BETWEEN '$lastyear' AND '$today'

Do I look at the which parameter in the WHERE clause will have the fewest records and put that first in the WHERE clause? Then the next fewest,...

MySQL Is Slow, Trying To Optimize
It just seems that my system is slow, adding records, etc. I tried to optimize it, saw that there was a my-medium.ini file, read that is was for medium sized system. I replaced it with my.ini (yes I did make a backup, thankfully) and restarted mysql.

Pretty much it hangs. I tried to connect with QueryBrower, did a Select Count(*) and just froze. I admit that I didn't wait for ever, yes I know I Select Count(*) takes a long time but I gave up after waiting 3x as long as I normal did. Also the logs screen froze in administrator.

Optimize Table With LOCAL Arg
I am running mysql 4.0.16. Optimize table does not work with LOCAL or
NO_WRITE_TO_BINLOG options, and gives me mysql error. Any ideas?

Optimize Select Statement
I have a big simple table with 3 fields and 700,000 records.

Table: mytable
Field1 Field2 Field3
name1 initial1 1
name2 initial2 0
name3 initial3 0
name4 initial4 0
... ... ...

i like to be able to do a fast search where it can return the first record that has 0 in field3. If i go through the whole table, it takes too long.

Select * from mytable where field3 = 0; <---- it takes too long to do this

i would like to get the record # 2 because this is the first record that field3=0

Usage Of OPTIMIZE Command
I had a table of more than 800'000 rows where I deleted approx 200'000 rows. The documentation says that I shouldn't normally have to use the OPTIMIZE TABLE command, but performance on this table has not improved. Is it a good idea to use it?

Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host.
When I ask the script owner, they told me that I need to optimize the db to make tables indexed.
I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.

Optimize A Select For Birthday
I need to select all the clients in my database that celebrate their birthday in the first/last half of the month. The table has an index on the birthday field. I used the next statement:

select buc from clientes where Month(FechaNacimiento)=5 and DAY(FechaNacimiento)<16

to obtain ~13,000 clients, but it takes a while to do it. The explain output says that it will use the index, but estimates the 10 million record scan.
Is there another way to express the WHERE clause to help MySQL optimize the search?

Optimize My Snort MYSQL DB
We are using SNORT IDS which connect to a MYSQL DB, the SNORT IDS has been running for long long time and it got a lot of data in MYSQL DB. Nowadays, the snort web interface has become extremely slow and my friend said it is bcoz there are too much data in MYSQL and MYSQL was not optimized for long time.
May I know how do I do some maintenance or optimize the MYSQL for it to run faster?
I used optimize table, and repair table command for all tables already but the web interface is still slow

Stop Optimize Table
While trying to shorten my files, I tried optimize table - later I
found that I need:

mysqlcheck -u root -p[password[ --all-databases --analyze --optimize
(thanks to Markus Popp).

But it gives this error:

mysqlcheck: Got error: 1044: Access denied for user 'root'@'localhost'
to database 'information_schema' when executing 'OPTIMIZE TABLE ... '

How can I stop this?
The optimise command seem to be in the way (and since I started it, my
computer has got pretty slow)

Optimize UPDATE Query With Subquery
Users(users_id,...,pictures_average_grade) - 5,000 rows
Pictures(..,average_grade,users_fk) - 50,000 rows

One User can have many pictures. Each picture have average_grade which is being continually changed. Once a day i would like to compute for each user average grade of his pictures and update column. users.pictures_average_grade. I wrote the following query:
UPDATE users AS u SET u.pictures_average_grade= (SELECT AVG(average_grade) FROM pictures WHERE users_fk=u.users_id).

The problem is that the query is being processing very long (after 15 minutes i resing and stop the query).

How To Optimize Mass Data Queries
I am trying to optimize the speed of a query that calculates bias/mean absolute error of forecasts against observations. The forecasts are issued hourly, and make hourly forecasts to the 48th hour. Furthermore, there are about 20 different forecast methods. On top of that, there are 12 different sites. What this adds up to is a really large database. The forecast table has 400K rows, while the observation table as 25K. In order to calculate the above statistics for a given time period, one must pull a lot of data based on the date range. The worst case scenario is that 60,000 rows must be pulled from the forecast table (all stations, methods, and 30 days of fcsts). The problem doesn't seem to be the speed of the query (<.1 sec) as the query is only on the Primary Key (time,station,method), but rather the throughput of the data. I am using PHP to handle the data and present it to the end user. I would estimate that about 15Mb has to go through the pipe before it's processed (ie. converted to an array, and compared to the observations - note, an observation would correspond to say, f010 of a forecast 10 hours earlier, so a parallel join is impossible). The machine I am using is forced to swap in order to execute the script, but it is an old machine (500Mhz processor, 128Mb Ram) that's used for development.

Are there any ways of possibly compressing the inbound data, especially while it is being processed? Right now, I create the big 4-D array before I do the statistics (which are grouped by method), would it help to split up the data feed by method? PHP seems to be most sluggish during the query mysql_query(...), rather than the mysql_fetch_array() looping.

How To Optimize MySQL 4.0.24-log On 10 Mbps Pnet
i hv a database driven portal which derives all the information & data from a database server which is on a different server and web server & database server are connected on 10mbps PNET.(Private Network)

i need to know by what all means and methodologies can i optimize mysql and make my site faster

this site is totally in LAMP environment:

php=4.3.2
mysql = 4.0.24
Apache=2.0....
RHEL3