>
>Does your master table have a list of table names and the table's
>characteristics? How is the search called?
currently i have a master table called "files", the primary key is id and
the data tables are called filedata1 filedata2, etc.
where 1 and 2 correspond to the file id. Each of the filedata tables
contains all the content to dynamically create a new file based on a linked
template.
file
id = 1, name="greenville", contact="bob", template=3, defdirectory =
"soccer/" etc...
filedata1
id = 29, type=1, data1="April 3, we won 6-4 against the bedford bears",
data2=null, data3=null
id=34, type=1, data1="Welcome to the greenville soccer team web page",
data2=null, data3=null
when the type=4 it looks for the text in the TextData file. However, when
there is much less text, it is stored in the filedata file itself in the
data1 column. data1 also contains the text that will be used in the alt tag
for images(type=2), and the "link's tags" for links (type=3), etc.
currently i am searching the TextData table using:
$sql = "SELECT files.location, files.filename, TextData.*,
MATCH(TextData.text) AGAINST ('$searchFor') AS score ";
$sql .="FROM TextData, files WHERE MATCH(TextData.text) AGAINST
('$searchFor') AND files.id = TextData.fid";
1) i want to be able to merge the FileDataX tables like this- only this
doesn't work: error near (SELECT CONCAT...
$sql = "CREATE TEMPORARY TABLE searchtable (id int, type tinyint, data1
varchar(255), data2 int, data3 int) TYPE=MERGE UNION=(SELECT
CONCAT('filedata',id) FROM files WHERE defdirectory LIKE '$defdirectory%'")
INSERT_METHOD=LAST";
and then perform my full text search on data1 where type=1?
<snip: jay>
>Seems like you would have to call multiple queries no matter what, unless
>you creat one large merged table for your several thousand small tables. Is
>this normalization gone overboard? Do all of the tables match in
>configuration (same # of columns, same datatype per column, etc.)?
</snip>
actaully - i thought about one BIG table, or even medium sized tables
however the primary use of the tables is to build one file from one
filedata table, with the future ability for files to reference each other,
to share data. to simplify the process, the editor reads in the entire
table, presents the old data, and then on update, it deletes the entire
table, and inserts the new data. otherwise, i would have to look for data
that was removed, create an array of deleted rows, and then run the delete
query, which actually takes lot more time than delete * from filedataX.
There are several other factors that caused me to go the route of several
filedata tables for each file.
i will also say - my normalization isn't as smart as i'd have liked, and
i'm thinking that i may move ALL the text into the textdata table, which is
3 columns
fid = files.id INT
line = filedatax.id INT
text = "whatever text" TEXT
but i have an index on my text column (required for the fulltext seach) and
am concerned that if it's tooo big, it'll be a very slow search! as well as
slowing down my editor too much, if got it down to about 20:1 small text :
large text.
ken
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
