Speeding up a query

Question

I have a database table which has over 1 million rows and could get bigger. I have a python script which queries the database to grab one random record from this database table. The query I am using is:

SELECT *
FROM customers
WHERE cust_type = 'C'
ORDER BY RAND()
LIMIT 1;

I was simply wondering if there is a better, faster way to do this?

Thanks to Michael Benjamin for his excellent answer: Below is my working Python script with his suggestions

def util_get_random_customer_individual():
    # Gets a random customer from the MySQL DB Customers table. Users will need to parse items from the results into
    # individual results
    # Connect to the DB
    config = {'user': 'user', 'password': 'password', 'host': 'host name',
              'port': 3306, 'database': 'database'}
    conn = mysql.connector.connect(**config)
    c = conn.cursor()
    type_code = 'I'
    # Get a random customer based on the the count
    c.execute('SELECT COUNT(*) FROM customers WHERE cust_type = %s', type_code)
    count = c.fetchone()[0]
    random_value = random.randint(1, count)
    c.execute('SELECT * FROM customers WHERE cust_type = %s LIMIT %s, 1', (type_code, random_value,))
    random_customer = c.fetchone()
    return random_customer

possible duplicate of [How can i optimize MySQL's ORDER BY RAND() function?](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) — krokodilko, Mar 01 '14 at 10:51

Michael Benjamin · Accepted Answer · 2014-02-28T23:56:09.197

3

Randomly generating a number based on the total, then using the offset is extremely quick.

SELECT COUNT(*) AS Total 
FROM customers 
WHERE cust_type='C';

PHP:

$rand = rand(1, $count);

SELECT * 
FROM customer 
WHERE cust_type='C' 
LIMIT $rand, 1;

edited Feb 28 '14 at 23:56

answered Feb 28 '14 at 23:50

Michael Benjamin

2,895
1
16
18

This doesn't work for me in MySQL. Doesn't like the `LIMIT number, number` – developerwjk Mar 01 '14 at 00:03
What is your MySQL version? [MySQL 4.1+](https://dev.mysql.com/doc/refman/4.1/en/select.html) supports it. – Amir Pashazadeh Mar 01 '14 at 00:13
What version of mysql are you running? Older versions might require `LIMIT 1 OFFSET ####` – Michael Benjamin Mar 01 '14 at 00:14
I'm running 5.0.22, on Redhat Linux – developerwjk Mar 03 '14 at 17:49
This works for me. I had to tweak the PHP a little since I am using Python, however this works really well. Thank you very much. – DarthOpto Mar 03 '14 at 20:47

Sam · Answer 2 · 2014-03-01T00:20:18.103

0

Try

SELECT * FROM customers 
WHERE id >= 
(SELECT FLOOR( MAX(id) * RAND()) FROM customers ) 
AND cust_type = 'C'
LIMIT 1;

edited Mar 01 '14 at 00:20

answered Mar 01 '14 at 00:00

Sam

2,761
3
19
30

That returns "Invalid use of group function" – developerwjk Mar 01 '14 at 00:03

Speeding up a query

2 Answers2