CODEWITHSUNDEEP
javamysqlregexperformancejdbc
AnotherNewbplusplus
AnotherNewbplusplus

Reputation: 215

What is more efficient? Using Regex or create new columns in mysql for same information

I am new to application design and I'm trying to think in terms of design and future scalability. Let's say we are starting with three groups of people who write notes to each other with updates. Currently the section of focus on my table has columns like this:

sampleId | officeNote | labNote | billingNote ...

When a new entry is made, my java program includes this format to each sampleId and whichever note category it was written to:

[MM/dd/yy HH:mm - user] userNote\r\n

Now if I want to produce an overview table in my GUI application so that any team can look into the note and understand why a sample is having some problem. This shortened table would have a format something close to this:

    Header: Office Note
----------
Sample Id | Date Created | User | First Line | Last Updated Line

Header: Billing Note
-------------------
Sample Id | Date Created | User | First Line | Last Updated Line

Header: Lab Note
-------------------
Sample Id | Date Created | User | First Line | Last Updated Line

Knowing how the final table outcome is....would it be wise to make more columns to support 'date created' and 'user' per each note group? Or would I make a regex in either in mysql or java to parse out the first date created and user.

Let me know what else I can supply to the question. And yes, rows will be deleted as soon as the notes are resolved.

Upvotes: 0

Views: 70

Answers (1)

C.Champagne
C.Champagne

Reputation: 5489

It would be much cleaner to separate it in several columns. In my opinion, using fields having always the same structure should be avoided if you always need to parse it. It looks like denying the use of a relational database.

Furthermore, I haven't tested it but it is more than probable that using regex is significatively less performent.

More importantly, you can use indexes. For example, if you need to display the records related to one user or starting from a determined interval, it will be much faster if you index respectively on the User field and on the Date Created.

Finally, you might need make complex queries in the future so you would have to use regex directly in your queries. This is not always supported! Thus you might have to "complete" it in your Java code, which is slow and quite ugly. Even when the functionality is available your DB, don't forget it makes it incredibly more complex and slow so it can become a nightmare for the development and the maintenance of your application.

Upvotes: 3

Related Questions