Page 1 of 3
Proper database table design?
Posted: Sat Feb 14, 2015 12:16 pm
by Fangbeast
A fair while back, I designed a recipe manager for myself and with InfraTec kindly doing the import routines for the various formats (Because I didn't have a clue), a reasonable program took shape.
However, 600,000 recipes later, the monolithic table design is seriously slower than Idle's distastefully abused sheep (I have the pictures!!). I mean, slower than the goat he tried to gene splice onto the sheep to give him more friction (hehehehehe).
Does anyone have an idea of better database design for databases of this size?
Never have done anything more complicated myself. But it's time I did!
Re: Proper database table design?
Posted: Sat Feb 14, 2015 12:29 pm
by idle
I can't help with databases right now but I'm currently ruminating about making an in memory db
but I'm still banging things round thinking about to support the unicorn nicely.
Re: Proper database table design?
Posted: Sat Feb 14, 2015 12:58 pm
by Fangbeast
idle wrote:I can't help with databases right now but I'm currently ruminating about making an in memory db
but I'm still banging things round thinking about to support the unicorn nicely.
That's the problem with unicorns, they are permanently HORNy and being a UNIcorn, they can't make much corn!
On a more serious note (That little pink one on the kitchen table), none of the free or commercial databases were fast after several thousand records, they all suffered from massive slowdowns.
When I added BEGIN TRANSACTION and COMMIT many years ago to all databases I make, the speed increase was phenomenal on inserting records but It doesn't work on queries.
I know I need to start using referred indexes rather than monolithic tables but I need some examples.
Re: Proper database table design?
Posted: Sat Feb 14, 2015 1:07 pm
by Shield
Fangbeast wrote:I know I need to start using referred indexes rather than monolithic tables but I need some examples.
It might be useful if you provide us with your existing table layout, maybe it can be restructured.
Re: Proper database table design?
Posted: Sat Feb 14, 2015 1:39 pm
by Fangbeast
Shield wrote:Fangbeast wrote:I know I need to start using referred indexes rather than monolithic tables but I need some examples.
It might be useful if you provide us with your existing table layout, maybe it can be restructured.
Code: Select all
Structure RecipeData My fields ; RecipeFox fields ; SQLite type
Version.s ; Recipe version ; ; TEXT
Owner.s ; Recipe owner ; ; TEXT
Title.s ; ; Title ; TEXT
; Description.s ; ; Description ; TEXT
Categories.s ; ; Categories ; TEXT
; Cuisine.s ; ; Cuisine ; TEXT
Serves.s ; ; Servings ; TEXT
Yield.s ; ; Yield ; TEXT
; PreparationTime.s ; ; Prep. time ; TEXT
; TotalTime.s ; ; Total Time ; TEXT
; Author.s ; ; Author ; TEXT
Source.s ; ; Source.s ; TEXT
; CopyRight.s ; ; Copyright ; TEXT
; ServingIdeas.s ; ; Serving ideas ; TEXT
; Wine.s ; ; Wine ; TEXT
; ImageUrl.s ; ; Image URL ; TEXT
Ingredients.s ; ; Ingredients ; TEXT
Instructions.s ; ; Directions ; TEXT
; Notes.s ; ; Notes ; TEXT
; CookTime.s ; ; Cook time ; TEXT
PictureName.s ; Local picture name ; ; TEXT
LastUpdate.s ; Last time updated ; ; TEXT
Favourite.s ; Marked as a favourite ; ; TEXT
Deleted.s ; Marked as deleted ; ; TEXT
Record.s ; Record number ; ; INTEGER PRIMARY KEY AUTOINCREMENT
EndStructure
My database structure is patterned around RecipeFox, a recipe capture program and the list in the structure denotes fields that I have implemented and fields that I want to implement.
I have laid out the columns to indicate the separate fields used and unused to make it easier to read, as well as their type.
Structure this then:):):) And, leave Idle's sheep alone, it's inhumane what he does to them, but they like it!
Re: Proper database table design?
Posted: Sat Feb 14, 2015 4:04 pm
by skywalk
Inserting records does not require comparisons beyond rowid so that is fast.
Your table/structure is mostly string and 1 index. So that is a lot of slow string compares. You need to map out the most frequent queries and try to optimize the result set with integer indexes.
Change many of the fields to integer: Record, serves, cuisine, yield, preptime, etc.
Then those numerics point to another small table containing the text.
Also, provide your top 3 SQL query.
Re: Proper database table design?
Posted: Sat Feb 14, 2015 9:53 pm
by JHPJHP
Hi Fangbeast,
As skywalk was saying index's on commonly queried fields can provide a significant performance boost.
Code: Select all
DatabaseUpdate(0, "CREATE INDEX idx_recipes_author ON recipes (author);")
Another thing to consider is running [ a ] VACUUM against the database.
-
https://www.sqlite.org/lang_vacuum.html
Re: Proper database table design?
Posted: Sun Feb 15, 2015 1:18 am
by Fangbeast
JHPJHP wrote:Hi Fangbeast,
As skywalk was saying index's on commonly queried fields can provide a significant performance boost.
Code: Select all
DatabaseUpdate(0, "CREATE INDEX idx_recipes_author ON recipes (author);")
Another thing to consider is running [ a ] VACUUM against the database.
-
https://www.sqlite.org/lang_vacuum.html
When creating indexes in SQLite, do they automatically get used or do I have to specifically reference them during searches?
As for vacuum, I have this turned on during database creation: "PRAGMA auto_vacuum = on"
@ SkyWalk Title, Categories, Source, Ingredients, Instructions, Notes are the most important search fields.
Re: Proper database table design?
Posted: Sun Feb 15, 2015 2:51 am
by JHPJHP
Hi Fangbeast,
You don't need to do anything but create them, the database will handle the overhead.
As skywalk mentioned, Integer is faster then String, but test slowly by adding index's to your existing layout one at a time, later switching the data-type and testing again; just be sure to have a good backup.
Cheers!
Re: Proper database table design?
Posted: Sun Feb 15, 2015 4:23 am
by Fangbeast
You don't need to do anything but create them, the database will handle the overhead.
Ah, good to know, thanks.
As skywalk mentioned, Integer is faster then String, but test slowly by adding index's to
As far as I know, SQLite stores all data as string/text so if I converted some members of my database structure to integer, i'd still have to convert to string to INSERT it wouldn't I?
At least, that's the way I remember it. Must have a look at the SQLite pages again.
Re: Proper database table design?
Posted: Sun Feb 15, 2015 4:55 am
by skywalk
I did not use SQLite 2.0 but from what I read, that version stored all data as Ascii.
3.0 and greater improved the db performance by applying type affinity per column such as TEXT, INTEGER, REAL, BLOB.
The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others. It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.
This way, "salt" and "sugar" is only stored once in the [master ingredients] table.
Then each recipe is calling out integer indexes from [master ingredients].
Re: Proper database table design?
Posted: Sun Feb 15, 2015 7:28 am
by Fangbeast
skywalk wrote:I did not use SQLite 2.0 but from what I read, that version stored all data as Ascii.
3.0 and greater improved the db performance by applying type affinity per column such as TEXT, INTEGER, REAL, BLOB.
The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others. It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.
This way, "salt" and "sugar" is only stored once in the [master ingredients] table.
Then each recipe is calling out integer indexes from [master ingredients].
I was afraid of that. You are talking about database "normalisation, something I've never done. Did some research on it recently and it was very complicated.
Re: Proper database table design?
Posted: Wed Feb 18, 2015 2:40 am
by Fangbeast
The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others.
Yes, this is called database 'normalisation' and I created an example someone on this page of how my database 'should' look based on that but I didn't know how to generate the complex insert statement to handle the data save (Still don't)
Went to a generic coding forum and asked for help on that and they all told me to search for 'database normalisation' which is why I asked them the question in the first place!
It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.
Yes, I realise that by seeing examples but the queries aren't as bad as I thought once I studied them.
I think this will require a massive amount of learning from me as it's beyond my current level of knowledge so I am looking all over the net for concrete examples of how to make complex inserts to save the data to this sort of table arrangement first.
Plenty examples of queries to get the data back but not how to insert it all to this arrangement (Unless I missed something) so will keep looking.
P.S I am up to 250,000 recipes in one SQLite database until the program I made just doesn't respond any more:):):)
Re: Proper database table design?
Posted: Wed Feb 18, 2015 10:10 pm
by fsw
Fangles, I know you can do it
Re: Proper database table design?
Posted: Thu Feb 19, 2015 2:31 am
by Fangbeast
fsw wrote:Fangles, I know you can do it
In my dreams maybe!!
Had to drop back into the NABBLE SQLite forum and ask for help (Where I should have been in the first place, but I forgot).
Sorted out how my normalised table structure has to look and I have to learn what a "Foreign Key" is before I can start to insert things this way. In my case, the FK will be the linked ID from the sub-table to the main table.
My tables are ready for "Foreign Key" setup as linked tables pointing to the main table are already setup with linked ID's just for this purpose.
The insert sequence is going to be a bitch. Check each step of the insert, check whether they returned success and all to be done in the right sequence. But, it was pointed out to me that the entire sequence need not be slow, all can be wrapped in BEGIN/COMMIT.
This is how the new table will look at the start. Needs a bit more normalising, especially when you think that the owner, author, source and perhaps copyright could potentially have thousands of duplicates.
But, it's a start
PK is primary key
FK is foreign key
Code: Select all
recipes table recipe_id ; Unique record number
recipe_name ; Recipe title
recipe_version ; Revision number
recipe_owner ; Recipe owner (home reference)
recipe_author ; Who wrote the recipe
recipe_source ; Where the recipe was sourced from
recipe_copyright ; Owner isn't necessarily the seller
recipe_lastupdate ; Last time this was updated
recipe_favourite ; Marked as a favourite
recipe_deleted ; Marked as deleted, to be purged
recipe_description ; Specific recipe description
recipe_notes ; General notes
recipe_servings ; Number of servings
recipe_peparationtime ; Preparation time
recipe_cookingtime ; Cooking time
recipe_totaltime ; Total time
;---------------------------------------------------
ingredients table ingredient_id PK;
ingredient_name ;
ingredient_price ;
recipe_ingredients table recipe_id PK;
ingredient_id FK;
ingredient_amount ;
;---------------------------------------------------
recipe_instructions table recipe_id PK;
recipe_step ;
step_description ;
;---------------------------------------------------
category table category_id PK;
category_name ;
recipe_category table recipe_id PK;
category_id FK;
;---------------------------------------------------
picture table picture_id PK;
picture_name ;
picture_url ;
recipe_pictures table recipe_id PK;
picture_id FK;
;---------------------------------------------------