Quote:
Originally Posted by opetopic
I don't think you need a separate database for randomized words. It isn't worth the extra space when there are easy ways to do it. First, do you select the 100 random words seldom (say at the beginning of a game)? If so, then you might be able to get away with the following query for getting 100 random words:
Code:
SELECT * FROM words ORDER BY RANDOM() LIMIT 100
This is usually a no-no, but with only ~150,000 the execution time should be negligible.
Also, drop the length column as it will not speed up your queries. If your columns are properly indexed searching for a word should be instant. Further, change the "word" column's data type to VARCHAR.
You say that you have an ASCENDING index on both the word column and length column. Such an index will be used only if you are using BOTH of those columns in an AND clause in your query. Just put one ASCENDING index on the word column.
Finally, after doing all of this run the VACUUM command on your database (I believe this is under Database > Compact Database in the Firefox extension you mentioned). See if that helps any...
P.S. I don't see any reason why you couldn't upload the sqlite file, but I don't know if the forum has attachment size restrictions. |
Hi opetopic,
i'm back with more quesitons
(work was overwhelming yesterday, not complaining, it's good to still have a job nowadays)
This is the link for my sqlite file, I have not ran VACCUM yet:
RapidShare: 1-CLICK Web hosting - Easy Filehosting
So it would also be better if I combined my dictionaries for other languages into one sqlite file and just use multiple columns?
Please let me know if my sqlite file is missing something such as KEYWORD.
Thanks again for all your help,
-FerrariX
