•  

Announcement

Announcement Module
Collapse
No announcement yet.

optimize 20 second query

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

  • optimize 20 second query

    #1
    [mysql 5]

    I have a simple table with some fields, one of theme is a mediumblob field.
    12.000 rows
    about 422 Mb.


    I just want to do a simple query like this

    SELECT *
    FROM tbl1
    WHERE condition
    ORDER BY field1 DESC;

    This query need 20 seconds to be done!

    If I avoid condition AND "Order By" or if I use a LIMIT 0,1, than the query take almost 1 sec, but I really need to select more than 1 record and use condition, order by and also join it with another table.

    How can be optimized this simply query?!
    Tags: None

  • #2
    It depends on your WHERE condition.

    But if your looks something like this:

    ...WHERE col1 = 'something' AND col2 = 'something else'ORDER BY col3


    Then you create a combined index on (col1, col2, col3).
    Note how all the columns of the WHERE condition is part of the index and that the order by is the last column in this index.

    Same principal if you have a join condition.

    Comment


    • #3
      sterin wrote on Mon, 12 November 2007 20:43

      It depends on your WHERE condition.

      But if your looks something like this:

      ...WHERE col1 = 'something' AND col2 = 'something else'ORDER BY col3


      Then you create a combined index on (col1, col2, col3).
      Note how all the columns of the WHERE condition is part of the index and that the order by is the last column in this index.

      Same principal if you have a join condition.


      So I need to create index for each columns I use in WHERE and ORDER BY, is this what you mean?

      Comment


      • #4
        Quote:


        So I need to create index for each columns I use in WHERE and ORDER BY, is this what you mean?


        No, you need to create _one_ index that contains all these columns.

        This example creates one combined index based on (column1, column2, column3)

        CREATE INDEX ... (column1, column2, column3);

        Comment


        • #5
          What is the differenc between seperate indexes and combined indexes? And in this thread there is a ORDER BY col3. So is tehre ny need to be index of col3 seperate? or is combined indexing enough?

          Best Regards,
          Supper

          Comment


          • #6
            The difference is that a combined index contains several columns which means that it is easier to find a certain record when you have more than one condition.

            Think about it this way.
            If you have a phone book that is only ordered by surname you will get:
            Doe, Richard
            Doe, Anna
            Doe, John
            Doe, Sarah
            ...

            And finding Doe, John in that data means that you will have to go through all the Doe's to check which given name matches.

            But if you create a combined index with (surname, given_name) it is much easier to find Doe, John since you know that he will be placed in alphabetical order after Doe, Anna and before Doe, Richard.

            No separate index of the col3 is needed since it is only used in the ORDER BY.
            And by placing it last in the combined index it means that MySQL can use the index to also retrieve the rows in sorted order and avoid a later sort of the matching rows.

            Comment

            Working...
            X