Not the answer you need?
Register and ask your own question!

very slow search query, help needed

abusiekabusiek EntrantCurrent User Role Participant
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 ;)

Comments

  • januzijanuzi Advisor Inactive User Role Mentor
    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' </pre>


    I hope this is what You need ?
  • MarkRoseMarkRose Contributor Inactive User Role Advisor
    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'</pre>


    ?

    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.