Improving table performance
In MySQL, I often miss some options like "signed / unsigned" ints and "allow null", but I'm wondering if these details can slow down my web application.
Are there any noticeable performance differences in these situations?
- using min / high range primary key Integer
- 5000 lines with IDs from 1 to 5000
- 5000 lines with IDs from 20001 to 25000
- Integer PK value uniformly increasing vs.
- 5000 lines with IDs from 1 to 5000
- 5000 lines with IDs scattered from 1 to 30,000.
- Setting Integer PC as unsigned and signed
- example: where unsigned gain is actually not needed
- Setting a default value for a field (any type) as default
- example: update a row and specify all field data
- Allow Null vs deny Null
- example: updating a row and all field data is given
I am using MySQL, but this is a more general question.
source to share
using min / high primary key range Integer * 5000 lines with ids from 1 to 5000 * 5000 lines with ids from 20001 to 25000
Irrelevant.
The PK integer value increases uniformly in vs unevenly. * 5000 lines with IDs from 1 to 5000 * 5000 lines with IDs scattered from 1 to 30,000
If the distribution is even, it doesn't matter.
Uniform distribution can help create a more efficient random sampling query, as described in this article on my blog:
It's the distribution that matters, not the boundaries: 1, 11, 21, 31
OK, 1, 2, 3, 31
no.
Setting integer PK as unsigned vs. signed * example: where no unsigned gain is actually required
If you declare PRIMARY KEY
as UNSIGNED
, MySQL can optimize predicates likeid >= -1
Setting a default value for a field (any type) by default * example: update a row and specify all field data
No difference.
Allow Null vs. deny Null * example: updating a row and all field data given
Columns with size zero more than one byte: index key for INT NOT NULL
long 5 , for INT NULL
length 4 .
source to share
From my understanding of B-trees (how relational databases are usually implemented, right?), These things shouldn't make any difference. All you need is a quick comparison function on your key, and it usually doesn't matter what range of integers you use (unless you choose the machine word size).
Of course, for keys, a single default or null resolution doesn't make much sense. In all non-key fields, allowing null or providing defaults should not have a significant impact.
source to share
5000 rows is almost nothing for the database. They usually use large B-trees for indexes, so they don't really care about primary key allocation.
Generally, should you use other parameters based on what you need in your database application. They cannot significantly affect performance. So use the default if you want the default, use NOT NULL
if you don't want the column to be NULL
.
If you have database performance issues, you should look for more important issues like missing indexes, slow queries that can be rewritten efficiently, making sure the database has accurate statistics about the data so that it can use the indexes correctly ( although this is the administrator's task).
source to share