mysql speed...

[mcalpine]

i have a table with 3,6 million records in it...
i have a column that may contain around 12000 of the same value in each record.
when i select data using this column, it takes 2 - 4 seconds...
i have an index on that column , but i need it even faster....

how can i make it faster...?

[tekky]

how precise are you making the query, and this may require downsizing the database and optimizing table design if not already at its fullest potential.....

[mcalpine]

its as simple as :
SELECT this FROM table WHERE that_stupid_column = 'whatever' GROUP BY this ORDER by somthing else...

myabe the group by and order by are causing the problem...

[tekky]

how about you show us the db structure? like column names and field types etc for the tables pertinent to this question.... over all that will be what slows things down I would say

[mcalpine]

word char(12), document char(12), rank char(8)
index on word.

i would select document where word = 'whatever' group by document order by rank

[tekky]

are either word or document unique, if so make one or the other a unique index

how this is looking though, you wont be able to speed anything up faster w/o breaking up the table into several tables like A-G on one H-L on anothe M-T on another and U-Z on the last...

[mcalpine]

this table is a reverse lookup table (i think thats what its called)....

when one word is searched .... its fast....
but im trying to devise a fast way to search 2 words.... or more...
and return documents that correspond to both words....

could you help me with some ideas?

thanks