Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which can be one of the five possible values. This parameter will never get new values.

What I need is the maximum possible speed and performance of select from this table. All other query types performance does not matter. Disk space doesn't matter much too.

Very important point is that in each select query I will always specify the value of z to only one value ( e. g. where z = 1 ) Also ib the select query I will always filter record by other fields and also I will make some joins.

The question is: will it boost the performance of select to split the table into five tables each corresponding to one of the five possible values of z? I am going to use MySQL database but I would like to hear the general answer if one exists.

share|improve this question
add comment

1 Answer 1

The question is: will it boost the performance of select to split the table into five tables each corresponding to one of the five possible values of z?

Yes, you could use partitioning here. Partition elimination will kick in. The partitioning will act as a leading index column.

Which brings me to the following point: Just add z as the leading column to all indexes. As you always filter on z (using an equality predicate) this will only help. (Except, if the index was unique anyway like an index on an ID column).

I'm generalizing a bit here like always with databases, but make sure to add filtering on z to your indexes.

Other techniques apply as well, of course, like making indexes covering.

I don't see the necessity for creating 5 tables, or using partitioning here. I can see how that might improve performance by a few percent, though, because z no longer needs to be stored.

If your app is generating enough load that a few percent of load matter, then your server is overloaded anyway by my definition. Still, it is worth a try if you don't care about increased development and maintenance costs.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.