If I had a table that 10 million entries in it, and there was a field in the table called “category” which housed a unique number from another table called categories, would it be faster for selecting records from this table via specific categories if the table was split apart into multiple tables via category?
So say something like this is the original.
item_table[]
- item_id, category_id, item_name
Then instead we create a bunch of these instead
item_table_category_id[]
- item_id, item_name
I’m thinking it might be faster to split the tables apart because it has less data to sort through, but then again it might be slower if you wanted to select data from multiple tables at the same time?
A primary key is one sort of index - you can add others so as to make the data more quickly available by other fields. Without an index on the field you are searching on it needs to go through all the records to find the ones that match. With the index it can basically access them directly via that field because the index lists all the values in order for that field and points to the corresponding records.
Because indices slow down UPDATE and INSERT queries. This is because MySQL uses B-TREEs for the indices, and needs to make sure these trees remain balanced for optimal performance. It may well be that one UPDATE/INSERT query causes the tree to become unbalanced, in which case it needs to be re-ordered, which can take a few seconds up until a few hours, depending on the number of rows. Of course the more indices there are, the more B-TREEs MySQL needs, the slower UPDATEs and INSERTs will be.