Re: search for query that result one random url from domain

[Bernd Meier]

laptop alias Wrote:
-------------------------------------------------------
> >I don't know about speed
>
> If you don't, no one does!
>
> But RJ touches on a fair point - one I overlooked
> - which is that a million rows is peanuts to MySQL
> so any way you cut it, your query should take no
> more than a few seconds.
>
> Try his query, and if it's still slow paste the
> result of EXPLAIN SELECT... here together with
> your CREATE TABLE statement.


nope takes more time at my database....

here is the explain select:

mysql> explain SELECT url
-> FROM
-> ( SELECT url, domain
-> FROM tbl
-> ORDER BY RAND() ) y
-> GROUP BY domain
-> ORDER BY NULL
-> LIMIT 10;
+----+-------------+-----------------------+------+---------------+------+------
---+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_l
en | ref | rows | Extra |
+----+-------------+-----------------------+------+---------------+------+------
---+------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL
| NULL | 934864 | Using temporary |
| 2 | DERIVED | tbl | ALL | NULL | NULL | NULL
| NULL | 934864 | Using temporary; Using filesort |
+----+-------------+-----------------------+------+---------------+------+------
---+------+--------+---------------------------------+
2 rows in set (42.39 sec)



here is the create table:



CREATE TABLE `tbl` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`url` varchar(500) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL,
`domain` varchar(250) NOT NULL,
`pr_domain` int(2) NOT NULL,
`pr_page` int(2) NOT NULL,
`level` int(1) NOT NULL,
`age` int(1) NOT NULL,
`tld` varchar(15) NOT NULL,
`listed` int(1) NOT NULL,
`external_count` int(20) NOT NULL,
`date` date NOT NULL,
`exported` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1003975



Edited 1 time(s). Last edit at 12/08/2011 08:59PM by Bernd Meier.