very slow search query, help needed


I have table fields with following structure:

id(int), document_id(int), name(int), value(text)
index(document_id, name, id)

with ~ 2000000 records growing fast. I have to search this table due to name and value but i need search only last ‘version’ of document (
by version i mean situation like this:

i have row
1 | 1 | first_name | Adrian
then i insert another
2 | 1 | first_name | John

this means i’ve edited document 1 and change first_name from Adrian to John and when in search user interface i put Adrian i should get no results

I’m able to get the latest value for each field in document with query very fast (0.07s):

select max(, d.document_id,, d2.value from documents d, documents d2 where = group by d.document_id,

When I add where clause like d2.value = ‘Adrian’ and = ‘first_name’ (with straight_join because otherwise it looks for the last appearance of value = ‘Adrian’ and it should check if the last version of that field has value = ‘Adrian’) execution time is about 15s.

select straight_join max(, d.documentId,, d2.value from documents d,documents d2 where = ‘first_name’ and d2.value = ‘Adrian’ and =

I’m out of ideas (

Please help me :wink:

How about second table ? You could create table with document id and name (is it int or varchar ?). Those two columns should be unique: name+document_id. Why name first ? I assume, that You look for documents that were changed by user with specified nickname.
With that table You could do something like:

insert into second_table (name, document_id) values (‘xxx’, ‘yyy’) on duplicate key update name = ‘xxx’

I hope this is what You need ?

Have you tried:

select *from ( select max( id, d.document_id did, name, d2.value value from documents d, documents d2 where = group by d.document_id, )where value = ‘Adrian’ and name = ‘first_name’


The inner query finds only the max id for each document_id, the outer filters it by name. adding another index on value will help.