List:General Discussion« Previous MessageNext Message »
From:Afan Pasalic Date:May 4 2007 8:17pm
Subject:Re: indexing order column
View as plain text  
Baron Schwartz wrote:
> Hi,
>
> Afan Pasalic wrote:
>>
>> Baron Schwartz wrote:
>>> Hi Afan,
>>>
>>> Afan Pasalic wrote:
>>>> hi,
>>>> if I have column order_id(int(4)) null do I have to index it too. 
>>>> I'm going to use it ONLY for sorting records.
>>>
>>> It depends a lot on how much data is in the table, etc etc.  An 
>>> index will make sorting more efficient in the general case when you 
>>> have a decent amount of data.  But it's hard to be specific with so 
>>> little information.
>>>
>>> Baron
>>>
>> I have table products (product_id is PK). I have table categories 
>> (cat_id is PK). since the product can be in more than one category, I 
>> have prod_cat table:
>> create prod_cat(
>>    cat_id int(8) unsigned not null,
>>    prod_id int(8) unsigned not null,
>>    order_id int(4) unsigned null,
>>    PRIMARY KEY (cat_id, prod_id)
>> ) engine=Innodb;
>>
>
> Okay, so your order_id is really sort order, not "id of the customer's 
> request to buy something."  (As an aside, perhaps "sort_order" would 
> confuse you less in the future when you don't remember the column's 
> purpose anymore).
>
> It probably makes sense to index the column if you want to use it for 
> sorting.  You could also just order by the primary key.  But I 
> understand there are times when you want to have a different ordering.
>
> Baron
yes, you're right. sort_order does make more sense :)
order by PK, in my case is, let's say impossible because I'm sorting 
products in ONE category. e.g.:
cat_id    |    prod_id    |    order_id
1   |   23   |   1
1   |   25   |   2
1   |   36   |   3
1   |   13   |   4
2   |   13   |   1
2   |   45   |   2
2   |   47   |   3
2   |   51   |   4
3   |   32   |   1
3   |   33   |   2
3   |   34   |   3
3   |   35   |   4

-afan






Thread
indexing order columnAfan Pasalic4 May
  • Re: indexing order columnBaron Schwartz4 May
    • Re: indexing order columnAfan Pasalic4 May
      • Re: indexing order columnBaron Schwartz4 May
        • Re: indexing order columnAfan Pasalic4 May