current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Basically I have a database of words,

This database contains a rowID(primary key), the word and word length as table columns.

I want to select a random row where length = x and get the word at that row.

This is for an iPhone game project and it is high priority that the queries are as fast as possible (the searches are made in a game).

For instance: 

SELECT * FROM WordsDB WHERE >= (abs(random()) %% (SELECT max(rowid) FROM WordsDB)) LIMIT 1;

This query is really fast at selecting a random row a lot faster than ORDER BY RANDOM() LIMIT 1, however, if I add the word length to the query I get issues:

SELECT * FROM WordsDB WHERE length = 9 AND rowid >= (abs(random()) %% (SELECT max(rowid) FROM WordsDB)) LIMIT 1

Presumably because the random row will not always have a length of 9.

I was just wondering what would be the fastest / most efficient way of doing this.

Thanks for your time

Note: the 2 % symbols are because it is in objective c and the query is set as a string.

share|improve this question
    
What are the issues? –  hamstergene Mar 27 '12 at 13:50

2 Answers 2

up vote 1 down vote accepted

This one seems to work ok for me:

select * from WordsDB 
where length = 9 
limit (abs(random()) % (select count(rowid) from WordsDB 
                        where length = 9)), 1;

note that length = 9 appears in both where clauses.

Add index on length if it appears to be slow.

share|improve this answer
up vote 1 down vote

Add an index to the WordsDB.length

create index if not exists WordsDBLengthIndex on WordsDB (length);

should make selection on this field much faster

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.