random row query optimization in innodb table

$offset = SELECT FLOOR(RAND() * COUNT(*)) FROM t_table
SELECT * FROM t_table WHERE LIMIT $offset,1

开发者_开发问答

This works great in myisam but i would like to change this table to innodb (all other db tables are innodb) to take advantages of foreign-keys and avoid table level locking.

The primaryId field of this table is a VARCHAR(10)

I can't "force" a numeric autoinc Id, because records are deleted/added all the time and a RANDOM(MIN(Id), MAX(Id)) prediction would probably miss rows lots of times.

how can i optimize this query to innodb?

Thanks in advance!

Arthur

---

This doesn't work for you?

SELECT * FROM t_table ORDER BY RAND() LIMIT 1

---

"SELECT * FROM t_table ORDER BY RAND() LIMIT 1 "

That will not work, because mySQL will check all the rows who match the condition (no condition here, so will take all the rows) they will copy the rows in a temporary table and then will select a random row