very slow search query, help needed

MySQL & MariaDB Other MySQL® Questions
1

Hi!

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.id), d.document_id, d.name, d2.value from documents d, documents d2 where d.id = d2.id group by d.document_id, d.name

When I add where clause like d2.value = ‘Adrian’ and d2.name = ‘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.id), d.documentId, d.name, d2.value from documents d,documents d2 where d2.name = ‘first_name’ and d2.value = ‘Adrian’ and d.id = d2.id

I’m out of ideas (

Please help me :wink:

2

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 ?

3

Have you tried:

select *from ( select max(d.id) id, d.document_id did, d.name name, d2.value value from documents d, documents d2 where d.id = d2.id group by d.document_id, d.name )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.