which is most efficient query?
Posted: Fri Aug 20, 2004 6:01 am
Hi,
Current situation:
A little theory before we acually do any implementation. Myself and another developer are unsure as to which is the best method in terms of speed to use for storing a number of ids against one record.
We have a table that records stoppages on waterways. Any one stoppage can exist across more than one waterway. We would also need at some point in the future to be able to query a waterway and find all the stoppages on that waterway.
Suggestions:
I have suggested that we store the waterway ids in a separate intermediate table which will record each stoppage against a waterway id. Two columns would exists: waterway_id and stoppage_id. So, if a stoppage is applied to three waterways then three records are created in the intermendiate table for the one stoppage [one to many]. Indexes can then be created on both fields in the intermediate table to aid searching.
The other developer has suggested storing the waterway ids as a csv string in a varchar column and then iterating over that column to find the relationships of waterways to a stoppage. An Index would be applied over the waterways column to help speed the searching.
My concern is that I would rather SQL do the work than php. For me, I think the query will be slowed by having to search through the csv values for each column rather than just searching over a column that will only contain one value, this may however be incorrect and so I would appreciate any feedback on which is more appropriate or a.n.other solution.
TIA
Current situation:
A little theory before we acually do any implementation. Myself and another developer are unsure as to which is the best method in terms of speed to use for storing a number of ids against one record.
We have a table that records stoppages on waterways. Any one stoppage can exist across more than one waterway. We would also need at some point in the future to be able to query a waterway and find all the stoppages on that waterway.
Suggestions:
I have suggested that we store the waterway ids in a separate intermediate table which will record each stoppage against a waterway id. Two columns would exists: waterway_id and stoppage_id. So, if a stoppage is applied to three waterways then three records are created in the intermendiate table for the one stoppage [one to many]. Indexes can then be created on both fields in the intermediate table to aid searching.
The other developer has suggested storing the waterway ids as a csv string in a varchar column and then iterating over that column to find the relationships of waterways to a stoppage. An Index would be applied over the waterways column to help speed the searching.
My concern is that I would rather SQL do the work than php. For me, I think the query will be slowed by having to search through the csv values for each column rather than just searching over a column that will only contain one value, this may however be incorrect and so I would appreciate any feedback on which is more appropriate or a.n.other solution.
TIA