CODEWITHSUNDEEP
mysqlperformanceindexing
userlite
userlite

Reputation: 499

mysql indexing performance

I've been trying to find concise answers to the following questions and I've read a lot but I'm still unsure. Maybe there are no simple answers because scenarios can be so different.

But here are the questions:

1) Should I index a tinyint column where all records are either a 0 or 1?

Example Query where postActive column has every record as a 0 or 1:

SELECT postId, postName, postTitle
FROM postTable
WHERE postDate > Now()
AND postActive = 1

2) Should I index an int column where all records have 0 except records that have an alternate int value that is unique in the table?

For this example, the column orderProcessingId is going to be 100% unique in the table (other than zeros), and the vast majority of records have 0 for orderProcessingId.

SELECT orderId, orderInformation, orderData, orderStuff
FROM orderTable
WHERE orderProcessingId = 38457237

In both these tables there are hundreds of thousands of records.

I guess the reason I'm question things is because it seems like indexing would depend on a relatively distributed frequency of values to improve performance. But in example 1 there is only extremely high frequency of both (and only two) values, and in example 2 there is extremely high frequency of one value (0) and no repeat frequency for the exceptions.

But my ideas are all based pretty much on conjecture... so. What can you tell me?

Upvotes: 3

Views: 574

Answers (1)

C3roe
C3roe

Reputation: 96151

I guess the reason I'm question things is because it seems like indexing would depend on a relatively distributed frequency of values to improve performance.

Not necessarily.

Should I index a tinyint column where all records are either a 0 or 1?

Depends – on whether the distribution of those values is largely equal, and if not whether you are mostly interested in the values occurring significantly less.

Should I index an int column where all records have 0 except records that have an alternate int value that is unique in the table?

Since you are trying to search for one of those rare and unique values – yes.

(I hope you are not confusing 0 and NULL here, btw. – because if your integer type entries were unique except for those with a value 0, you could not use a UNIQUE index here.)

Upvotes: 3

Related Questions