Announcement Module
No announcement yet.

very slow search query, help needed

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

  • 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

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