Skip to main content
A
Anonymous
April 21, 2009
Question

Querying an indexed database column. How?

  • April 21, 2009
  • 2 replies
  • 485 views

Hi there,

I am pretty good at CF and okay at SQL.

Over the years I have been informed that when using the same column in a database multiple times to do look-ups....e.g. a primary key field....that it would be best to "index" this column to improve speed.

However, I'm not fully sure what or how to index a database column. I have a few questions:

  1. Is it something to do in MySQL directly? Or can it be done programmatically with CF?
  2. Does it really speed things up a lot?
  3. How is new data entered affected? Does that new data get included into the index?
  4. Does my cfquery syntax have to be any different than is normal?

Any help or advice would be great.

I'm using CF8 and MySQL5 (query browser tools etc)

Thanks,

Mikey.

    This topic has been closed for replies.

    2 replies

    ilssac
    ilssac
    Inspiring
    April 21, 2009

    Indexing is a database topic.  As databases can be controled by SQL commands it would most likely be possible to do so inside of a <cfquery...> block, but I would only do that in rare circumstances.

    Indexing a table will almost always speed up frequently used database columns.  But when, how and why one would index a database column is a big topic on which entire papers, books and college classes have been developed around.  I would start with some reading of you database documentation an then branch out onto the internet.  You will find much written and should be able to educate yourself easily.


    D
    Dan_Bracuk
    Inspiring
    April 21, 2009

    Indexes speed up queries immensely when tables with many records are involved.  The kicker with the data is that you might not be able to have indexes on a field with null values.  Check your mysql documentation.

    Indexes are created with a create index query.  You may or may not be able to do this with cfquery.  You should still refer to the mysql documentation for not only the exact syntax, but to see if there are other considerations, such as type of index, where you store it, etc.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices