Re: Question on a select

Vincent Hikida wrote:
>
>>
>> The indexes are:
>>
>> CREATE INDEX file_info_#_display_idx ON file_info_# (file_type,
>> file_parent_dir, file_name);
>> CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name,
>> fs_parent_dir, fs_type)
>>
>> Are these not effective for the second query? If not, what should I
>> change or add? If so, would you have any insight into why there is
>> such an incredible difference in performance?
>>
>
> I didn't look at your indexes closely enough. When you have concatenated
> index, you want to have the most selective colum first. I guess that
> file_type is not very selective. file_name is probably the most
> selective. In the above, the index on file_set_# is optimal. The index
> on file_info_# is suboptimal.
>
> However, if the query is doing a hash join or sort merge, an index is
> not used so the index doesn't matter. However, you probably do other
> queries that do use the index so it should be fixed.
>
> Vincent

Thank you, Vincent!

I didn't realize that the order made a difference. A sign of how much
learning I need to do. :p For reference, I think 'file_parent_dir' and
'fs_parent_dir' are the most important because I do an 'ORDER BY
[fs|file]_parent_dir ASC' on most queries. I've made the changes, thank
you again!

Madison