In terms of read performance, you won't see much difference splitting
them up within the same table. What will degrade the write performance
is adding of additional indexes since they will need to be updated on
every write. Indexing will obviously speed up search performance
(~read).
I think this is a question you really need to answer yourself based on
your data access patterns. If searches on the substring will be few and
far between and the user can wait while the database does a full table
scan, then don't split it. If there is a low cardinality on the
substring data, then the indexes may end up being ignored anyway and a
full table scan performed. If full table scan takes way too long, then
split it and use indexes.
How often does the data change? If it hardly changes at all, then
create a separate table that replicates the data in split form and
index the parts there. You then implement logic in your code to search
the 'substring' table instead if the user is searching on a substring.
This is sort of taking a page from data-warehousing.
On Jul 8, 2005, at 5:50 AM, Mathias wrote:
I have an index char column in the format
aaaa-bbbbbb-cc-dd
where a,b,c and d are integers
Most of the time, the whole column will be searched for. But it will
sometimes be necessary to search for the substring in b or d (or a
combination of both) Thus if I split them up, they will need to be
indexed as well.
My question is: in terms of read and write performance, is it better
to split them up into 4 indexed columns of integer instead or to keep
them in one indexed column of char?
If they should be kept in one column, what is the quickest way to
search? To use a regular expression?
- Mathias
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
