Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Question Unanswered: ORDERing BY indexed column

    Hello!

    I need help concerning performance of particular SQL query (provided below). Could anyone, please, give me some clue, why the execution time of first query (provided below) is so long, while execution of second query (provided below) is almost instant. Maybe there is any way to optimize the first one? For example, using index on some or several columns, or performing some table optimization routines, or something else.

    All activities are performed in the single table.

    First query:
    select distinct [PRIMARY_KEY_BIGINT(20)] from [TABLE] where lower(concat_ws(' ', [VARCHAR(30)], [VARCHAR(60)])) like 'const_chars%' order by [BTREE_INDEXED_MEDIUMINT(9)] asc limit 20, 20

    Exec time: ~30 secs

    EXPLAIN statement on this query:
    id: 1
    select_type: SIMPLE
    table: [TABLE]
    type: index
    possible_keys: NULL
    key: [BTREE_INDEXED_MEDIUMINT(9)]
    key_len: 4
    ref: NULL
    rows: 202848
    Extra: Using where

    Second query:
    Idetical, except ORDER BY criterion is [UNINDEXED_BIGINT(14)]:

    select distinct [PRIMARY_KEY_BIGINT(20)] from [TABLE] where lower(concat_ws(' ', [VARCHAR(30)], [VARCHAR(60)])) like 'const_chars%' order by [UNINDEXED_BIGINT(14)] asc limit 20, 20

    Exec time: ~0.5 sec

    EXPLAIN statement on this query:
    id: 1
    select_type: SIMPLE
    table: [TABLE]
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 202848
    Extra: Using where; Using filesort


    Some other facts.
    1) if ORDER BY criterion is removed from the first query, execution time of the query becomes almost instant (~0.5 sec). Will provide EXPLAIN results upon request
    2) there is ~200000 entries in the table
    3) MySQL server version is 5.0.41-community
    4) it seems that older versions of databases (lots of inserts/deletes performed) takes more time to process the first query (up to several minutes on db, which was created several month ago), while younger versions takes about 1.5 sec
    5) OPTIMIZE/ANALYZE statements on this table did not helped.


    Thank You very much

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I'm taking a guess here, but the second query is an ALL type, while the first is an index type, meaning it has to examine the index, then rewrite all the ordering, whereas the latter query is ordered while examining.
    n.b. guess NOT fact...

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    Thank You for the reply.

    I wonder, why the execution time so depends on how long the database was used.

    Again, if sorting by unindexed columns goes so fast, is it possible to tell optimizer not to consider index in this (first) query? I have tried to add "IGNORE INDEX" clause while experimenting, but either it was ignored (explain brought the same results), either I wrote it wrong.

    Thanks

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The thing is the first query identifies the fact that it can use an index, but STILL does a full table scan. The 2nd query uses no index ( thus no index overhead ) and does a full table scan also ( i think ).

    Remove the DISTINCT and replace with a GROUP BY [PRIMARY_KEY_BIGINT]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •