MySQL Forums
Forum List  »  Newbie

Re: What would be faster?
Posted by: Peter Brawley
Date: July 23, 2007 09:50AM

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.

Options: ReplyQuote


Subject
Written By
Posted
July 23, 2007 05:44AM
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.