Database Indexing

[skatamatic]

I am just wondering if anyone has any pointers on how to index my database. It's for a piece of instrumentation software that loads in large amounts of sensor data (sometimes over 3 days worth of recording at 1 second intervals for 90 sensor's doubles). Right now it can load a 3 day job and graph it in about 15 seconds, I am looking to make that closer to 3 seconds.

Heres the tables involved:

JobHistory
---------
ID - UINT PK Autoincrementing
JobName - String
JobSummaryData0 to JobSummaryData15 - String
(There's some other data related to the job, not the sensor data though)

JobData
--------
ID - UINT PK Autoincrementing
JobID - FK Linking to JobHistory ID
Data0 to Data99 - Sensor data

Right now I am indexing JobData by ID and JobID in a binary tree. I am somewhat of a database noob, so am open to any sugguestions as to how to improve the performance. We use MySQL as our DB server running on the localhost using the InnoDB engine. To fetch the data we display the whole jobhistory in a gridview and use Select * from JobData where JobID=@myID .

I don't really know if this belongs in the C# forum, but there seems to be a lot of DB stuff on here and it seems to get more activity than the DB forum.

[kvprajapati]

Separate the index for each column. I guess the ID and JobID are two separate indexes and don't use UINT column if a MEDIUMINT or smallint is large enough to hold the values you need to store.

You can use EXPLAIN for any slow running query, always use explain to try figuring out what's going on.

[Momerath]

adatapost has it right. Separate indexes for each column. I do have to ask what you mean when you say you use a binary tree for indexing. MySql (last I checked) uses an unordered hash for indexing.

And if I understand you correctly, you are loading over 3 days of data per sensor? That's 60*60*24*3 = 259,200 data points per sensor. Not sure what you are graphing that on, but I doubt it has enough resolution to display. We have a high resolution printer that can do 4800 dpi, and that would still be over 50 inches wide to display. Maybe you can come up with a method to reduce the number of points you actually have to deal with.

[skatamatic]

adatapost has it right. Separate indexes for each column. I do have to ask what you mean when you say you use a binary tree for indexing. MySql (last I checked) uses an unordered hash for indexing.

And if I understand you correctly, you are loading over 3 days of data per sensor? That's 60*60*24*3 = 259,200 data points per sensor. Not sure what you are graphing that on, but I doubt it has enough resolution to display. We have a high resolution printer that can do 4800 dpi, and that would still be over 50 inches wide to display. Maybe you can come up with a method to reduce the number of points you actually have to deal with.

We use a pretty powerful graphing control that supports zooming in down to the single point, so while not every point needs to be displayed all at once, it all has to be loaded into memory (I don't want to have to requery the database for each zoom operation since the system must be able to be accessed offline after the graph is loaded). And MySQL supports a few different indexing methods - for this I selected Binary Tree. Thanks for the tips both of you!