2

So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store basis and resets at midnight, but that's besides the point.

As it stands, queries on this table take upwards of one minute and I can't figure out why. I have added indexes on columns that have high selectivity ratio but it hasn't made any difference. So, if I add an auto increment 'id' field for each row, would that speed things up, or are the queries slow because there are so many columns and so much unique data?

  • 3
    What RDBMS? it will make a difference as each one has nuances. And, posting the DDL of the table and a representative query would also help. Sometimes things are buried in the details that generic answers (like what you are going to get with the present level of info) might not account for. – Solomon Rutzky Nov 15 '14 at 22:54
  • 3
    While we wait for more details, I should point out that "each transaction number is only unique on a per store basis" means: if there is a StoreID or similar identifier for a particular store in this table, then you do have a means of unique identification that could be used as a PK. A PK doesn't need to be a single field. But also, a PK is a logical construct and won't help performance on single-table queries (though the index that often comes with them usually does, at least for queries that filter on those fields). – Solomon Rutzky Nov 16 '14 at 2:00
3

The reason we specify keys for a table is primarily to improve the data integrity and usefulness of the data. Keys guarantee the table is free from duplicate data and therefore they allow the user/consumer of the data to identify information correctly. DBMS query optimizers and storage engines are designed to take advantage of keys so having a key will also give your DBMS the best chance of executing some queries efficiently but there's no guarantee that adding a key will improve performance in every case.

As others have pointed out, based on your description it seems like you may have identified suitable columns to make a candidate key.

4

No, having a surrogate key would not speed things up.

You should consider what your queries are doing, and whether your indexes are sufficient.

For instance, if you are trying to find out the total sales for Widgets in Timbuktu on Christmas Day, then do you have an index on ProductID, StoreID, TransactionDate that also includes the SalesAmount? If not, how will that index manage to help you sum the SalesAmount column?

Also consider Sargability. To query against TransactionDate for records during December, you shouldn't apply any functions to the column, you should do something like: AND t.TransactionDate >= '20131201' AND t.TransactionDate < '20140101' because while you may have an index which involves TransactionDate, you almost certainly don't have an index involving 'TransactionDate converted to a month'.

4

Having a primary key per se will not speed up queries. Primary key constraints are usually accompanied by a unique index. If this index matches query predicates or join conditions then those queries are likely to run faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.