Geek Answers Handbook

The order of the queries on rand () is too slow

I have a large table in the database with offers (over 300,000 rows).

when I execute the following query, it takes 3 seconds.

$sql = "SELECT * FROM `offers` WHERE (`start_price` / `price` >= 2) ORDER BY RAND() LIMIT 1";

Table offers

`id` int(11) NOT NULL,
`title` text NOT NULL,
`description` text NOT NULL,
`image` text NOT NULL,
`price` float NOT NULL,
`start_price` float NOT NULL,
`brand` text NOT NULL

Is there any way to do this faster? I want to select one random line ( start_price/ price> = 2)

+4
source share
3 answers

I think your problem is that your query requires a full table scan for the sentence WHERE. order bymakes everything worse - depending on the volume passing through the filter.

You might want to keep this number in a table and add an index to it:

alter table offers add column start_to_price float;

update offers
    set start_to_price = start_price / price;

create index idx_offers_s2p on offers(start_to_price);

Then your request could be quick:

SELECT o.*
FROM `offers` o 
WHERE start_to_price >= 2
ORDER BY RAND()
LIMIT 1;

- , WHERE:

SELECT o.*
FROM `offers` o CROSS JOIN
     (select COUNT(*) as cnt from offers where start_to_price >= 2) oo
WHERE rand() <= 10 / cnt
ORDER BY RAND()
LIMIT 1;

10 .

, , .

+3

- , :

?

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

, , start_price price .

, , utf8mb4 utf8, / , , general_ci unicode_ci:

utf8_general_ci utf8_unicode_ci

, MyISAM (http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb), , , InnoDB , , MyISAM:

https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-than-one-core?lq=1

, :

[mysqld] // Don't play here unless you have read and understand what is going on
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_buffer_pool_size=2G

- : https://www.percona.com/software/mysql-database/percona-server/benchmarks

:)

+1

. : -

http://jan.kneschke.de/projects/mysql/order-by-rand/

, id, ( > =) 1. , , , .

, id ,

, id: -

SELECT offers.* 
FROM offers 
INNER JOIN 
(
    SELECT RAND( ) * ( MAX( Id ) - MIN( Id ) ) + MIN( Id ) AS Id
    FROM offers
    WHERE (`start_price` / `price` >= 2)
) AS r2
ON offers.Id >= r2.Id
WHERE (`start_price` / `price` >= 2) 
ORDER BY offers.Id LIMIT 1
0

More articles:


All Articles