0

I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.

I am creating a custom app where the user will be entering data into a table(lets call this table "manifest") at least 1 entry every minute, each entry will have data grabbed from the input field and when adding the new row, the current date and time is also being added to a datetime column.

This table will later be browsed by the app users on the front end at least 4 times a week when they come to do some exporting of their previously entered data.

I expect them to a lot of ordering by date, so I was planning to do an index on the date column for the first 8 characters of the MYSQL DATE = 00/00/00 (id leave out the time portion).


From what I know about indexes is that they slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered.

This sounds like it would be inefficient to create an index(other than the primary key[id]) if the users will be inputting data at least once a minute for maybe 2 hours straight(per day) and the index would have to be rebuilt every minute(?) new data comes in.


I know this table would just keep growing because they need to keep track of the records for years on end, that's why I'd want to put an index on the date column to make it future proof in terms of speed. But the fact that they'd be inputting data into it so frequently and indexes would have to be rebuilt every time has me a little lost on how to proceed.

I was thinking a non-clustered index would solve the issue? I've watched several videos explaining the two and it seems that the rebuilding would still need to take place for non-clustered indexes?

Any help and performance tips would be appreciated

New contributor
Uriahs Victor is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • you enter and update3 once or twice, but your read the data regularly, so the time lost in insert and update, is gained again when you make some selects, which are fatser because of the index. – nbk Jun 30 at 23:58
  • @nbk There would be updates happing every minute or so, the table would be responsible for logging the inputs entered by the users. They then come back to do some exporting at least 4 times a week, so there would be more inserting than searching – Uriahs Victor 2 days ago
  • tables are per definition unsorted, without index, big query need a lot of time to search allrows, that is why we add indexes, you case is special and if the people who make the select query don't mind waiting rather long. You can genearte 4 million rows and try your querys, to get a feeling. Also as long you don't enter data in a few microseconds, there shpuld be that of a problem. innodb locks only rows for insert. – nbk 2 days ago
0

About PRIMARY KEYs:

  • Each table must have a PRIMARY KEY.
  • The PK (in MySQL's InnoDB engine) is always "clustered"
  • Indexes other than the PK are non-clustered.
  • The PK is "unique". It uniquely identifies each row.

In your question:

  • One INSERT per minute is very slow. (So, no performance concerns here, regardless of the indexes on the table, regardless of anything else.)
  • "Exporting" rows -- Do you mean "read" (SELECT) or "archive" (SELECT, then DELETE)?? (More details, please.)
  • "first 8 characters of the MYSQL DATE = 00/00/00" -- Are you not storing the DATE part? If not, then make it a TIME column. If you want to "extract" just the time (from a DATETIME, see the TIME() function.
  • "indexes ... slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered." NO, NO, NO. Indexes are not rebuilt except on explicit requests -- which are rarely warranted.
  • Having an index does slow down writes slightly, but the index is likely to speed up reads significantly. So, don't fear having indexes.
  • After 10 years, you will have about 5 million rows. As tables go, this is only medium-sized. Not a problem. However, for accessing individual rows or groups of rows, indexes (whether PK or secondary) are strongly advised.
  • "I'd want to put an index on the date column to make it future proof in terms of speed." -- That does not "future-proof" it. It will help some SELECTs, but not others. Show us the selects so we can recommend a set of indexes that would be optimal for those selects.
  • Later, if you add/drop/change an index (primary or secondary), there will be some amount of effort, possibly involving copying the table over. It is premature to worry about that.
| improve this answer | |

Your Answer

Uriahs Victor is a new contributor. Be nice, and check out our Code of Conduct.

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.