Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Can SS Set Order of Table Columns?


Reply


3 Posts   1176 Views

Avatar
_Vince

Community Member, 162 Posts

9 July 2009 at 10:03am

I've always been told that, when laying out database tables, the columns that are keys should be at the top of the column list in order to speed up processing. In other words, if we have a table of customer addresses, and it's linked to a customer table by some column called "CUSTID", then the "CUSTID" column should be first up and all the address columns should come after.

eg:

CUSTID
ADDR1
ADDR2
.
.
SHIPPING ADDR1
SHIPPING ADDR2
.
.
BILLING ADDR1
BILLING ADDR1
.
.
and so on.

I guess the idea is that, when performing a query, the engine doesn't have to wade through a lot of data to find the keys required for the query.

I've been wondering if it's possible to implement this in SS. I've tried putting the $db array AFTER the $has_one array in the DataObject definition but I still wind up with the contents of the $has_one array after the $db. Short of hacking the core code, is there any way of setting the order of table columns?

Just wonderin', is all... :)

Avatar
banal

Community Member, 901 Posts

9 July 2009 at 10:33am

To be honest, I never heard of such a thing. I think that current RDBMS would optimize such things automatically. Mostly, you can gain DB Performance by carefully choosing the key indexing methods/algorithms.
In the case of SilverStripe where you're dealing with rather small data-sets, DB optimization will most likely not result in a noticeable performance gain.
Reducing the amount of requests per page and efficient caching are probably things I'd look into when I wanted to optimize a site. But then again: One should avoid premature optimization...

Avatar
_Vince

Community Member, 162 Posts

9 July 2009 at 4:48pm

Reading your answer, I had a bit of a google around to see WHY I had this idea and I really can't find anything on the topic, OTOH, example tables are usually depicted with the keys up the top.

Maybe it's something that used to make sense but not now, or maybe I'm not good with google! :D

Anyway, yeah... SS datasets aren't humongous so that's why the question's a bit of an idle curiosity on my part.