[lem]

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

[aschk]

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

[lem]

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

[aschk]

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]