# Table Indexing

**URL:** <https://forum.inductiveautomation.com/t/table-indexing/1760>\
**Category:** Archived Topics\
**Created:** [April 15, 2010, 4:23pm UTC](https://forum.inductiveautomation.com/t/table-indexing/1760 "2010-04-15T16:23:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 15, 2010, 4:23pm UTC](https://forum.inductiveautomation.com/t/table-indexing/1760/1 "2010-04-15T16:23:14Z")

</div>

When a group is created and run for the first time the table is created automatically. Could an option be provided to automatically create a clustered index on the t\_stamp column? For non DBA’s like me it would save a huge learning curve in trying to figure out how to improve performance.

If this is something you consider valuable could it be added to the Ignition platform as well?

Thanks,

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [April 16, 2010, 4:21pm UTC](https://forum.inductiveautomation.com/t/table-indexing/1760/2 "2010-04-16T16:21:19Z")

</div>

Well, it currently creates an index on that column, but whether or not it’s “clustered” (and what that even means) is going to depend on the database.

Unfortunately, we always have to try and do things that will work for all databases. We have the idea of “database translators” that help us navigate differences, but it’s not fine-grained enough to say “use this syntax for indices” and “use this syntax for the timestamp column of a table”. The difference would be necessary in this case because you can only have one clustered index per table, and in many cases we create multiple indices for a given table.

I think it’s worth noting that your original performance problem of having lots of indices on that table (include the 4-way index) was the result of someone’s actions- FactorySQL doesn’t create multiple-column indices.

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 16, 2010, 4:41pm UTC](https://forum.inductiveautomation.com/t/table-indexing/1760/3 "2010-04-16T16:41:42Z")

</div>

Yeah, I understand that creating a software package that works on multiple platforms with multiple databases puts limitations on what can be done automatically.

In terms of the performance improvements. Simply deleting the indices that IT created did not make that much of an improvement. As I understand from this experience; MS SQL created the clustered index on the PK column automatically and the real performance increase was to change this have the t\_stamp column clustered.

Am I interpreting this correctly?

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [April 16, 2010, 6:38pm UTC](https://forum.inductiveautomation.com/t/table-indexing/1760/4 "2010-04-16T18:38:36Z")

</div>

Yeah, I can believe that. That’s another trick about indexes… it’s really quite important that they’re constructed based on how they’ll be used.

With a historical table, the most common query is “where t\_stamp\>x” or “t\_stamp\>x and t\_stamp\<y”. Having the t\_stamp column indexed as part of a multi-column index probably won’t help, since you’re not using those other columns in the query. This is a huge misconception about multi-column indices that people often make. They think that they can just throw any column they might want to query on into a single index and get some benefit. The exact result is dependent on the database (and in fact the data engine, in a situation like mysql that supports multiple), but it generally will not be helpful- and in fact could hurt- if you’re not querying on all of the columns together (all columns in the where clause).

So, creating a single index on t\_stamp in your case was a good idea. As for clustering- a clustered index dictates how data is stored. For a historical table like this where you’re querying ranges of dates on the t\_stamp, it makes a lot of sense to store that similar data close together. Of course, practically speaking your ndx column should have lined up pretty well with the order of your timestamps (because the data is likely being inserted in order), but there may be some subtleties of how the db can optimize on the fact that you’re filtering based on the actual column that’s clustered.