Re: Newbie Trying to Expedite Query
Thanks for the response Mike.
Unfortunately, I cannot limit the number of columns. We are monitoring very complex equipment, which has hundreds of sensors, each reporting their data continuously. I am using a .Net app which writes that data into my table twice per second. So far, everything is working fine on that end.
The good news (for me) is that we don't actually use Workbench to retrieve the data in production. Our user runs a query from an Excel workbook I designed, which executes the query and loads the records into a spreadsheet. The sheet is customized to give graphs and trending information over time (yes, we do turn off calculation while downloading the records into the worksheet).
I discovered this issue because we are doing full table scans when we retrieve the data, and it is taking about a minute or more in production, where there are several hundred thousand records. We are starting a load test soon, running 24/7 for 2 weeks, and there will be 2-3 million rows added. I need to avoid the full table scan in that scenario. I was experimenting on my DEV DB with adding indexes to speed up the query. I am using Workbench, so hence the slower retrieval, with only 49,000 records in my DEV DB. I did try with retrieving just a few fields, and you are of course right, as the data was back in under 2 seconds.
I am confused by Workbench, as in my experience with SQL Server or Oracle (using Toad), the UI gave a total time to return the results, so I was expecting to see 50 seconds somewhere in the output pane.
Also, not being a DBA, I guess I mangled the table definition, as the indexes look messed up. Would you suggest I remove the indexes, and replace them with a single primary key composed of run_date, run_number and record_id (the auto-incrementing column)? It seems this would avoid the full table scan, as users always retrieve by run_date and run_number. Frankly, I don't know if we even need a primary key, as long as we have an index to avoid the full table scan.
The users are fine with the data retrieval taking a minute or longer if needed, as they do this retrieval on demand, occasionally.
Would you help a newbie out and suggest the index structure which would best suit our needs as stated?
Thanks...