Re: What would be faster?
Try this:
CREATE TABLE ip (
src int(11) NOT NULL,
dest int(11) NOT NULL,
typ smallint(6) NOT NULL,
KEY src (src,dest)
) ENGINE=MyISAM;
CREATE PROCEDURE makeints( in size INT )
DETERMINISTIC
BEGIN
DECLARE ctr INT DEFAULT 0;
DROP TABLE IF EXISTS ip;
CREATE TABLE ip (
src INT NOT NULL,
dest INT NOT NULL,
typ SMALLINT NOT NULL
);
REPEAT
INSERT INTO ip
VALUES ( ROUND(1000000000*RAND(),0), ROUND(1000000000*RAND(),0), ctr % 10);
SET ctr = ctr + 1;
UNTIL ctr = size
END REPEAT;
END;
CALL makeints( 1000000 );
-- will read a trillion rows
EXPLAIN
SELECT * FROM ip
WHERE src >= (SELECT FLOOR( MAX(src) * RAND()) FROM ip )
ORDER BY src LIMIT 1;
+----+----------------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+----------------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | ip | index | NULL | src | 8 | NULL | 1000000 | Using where |
| 2 | UNCACHEABLE SUBQUERY | ip | index | NULL | src | 8 | NULL | 1000000 | Using index |
+----+----------------------+-------+-------+---------------+------+---------+------+---------+-------------+
-- will read a million rows
EXPLAIN
SELECT * FROM ip ORDER BY RAND() LIMIT 0,1;
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| 1 | SIMPLE | ip | ALL | NULL | NULL | NULL | NULL | 1000000 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
-- will read one row
EXPLAIN
SELECT *
FROM ip r1
JOIN (
SELECT CEIL(RAND() * ( SELECT MAX(src) FROM ip )) AS src
) AS r2 ON r1.src >= r2.src
LIMIT 0,1
+----+-------------+------------+--------+---------------+------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+-------+------+------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | ip | ref | src | src | 4 | const | 1 | Using where |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+------------+--------+---------------+------+---------+-------+------+------------------------------+
PB
Edited 1 time(s). Last edit at 07/23/2007 12:32PM by Peter Brawley.
Subject
Written By
Posted
Re: What would be faster?
July 23, 2007 09:50AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.