GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

very slow search query, help needed

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • very slow search query, help needed

    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

  • #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 ?

    Comment


    • #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.

      Comment

      Working...
      X