|
|
Posted by Norman Peelman on 01/27/07 15:55
Paul Lautman wrote:
> Norman Peelman wrote:
>> Szymon Bobek wrote:
>>> Hi!
>>>
>>> I have databese with a few tables named like that: films_triller,
>>> films_horror, films_action etc.
>>> I decided to create many tables instead of creting just one big table
>>> containing all sort of films. Is it a good idea? (I think, that it
>>> boost search time much - am I right?)
>>> Now - to find a film (when only title is known) I have to search in
>>> all that tables. But I have a problem with that.. How to do that?
>>> SELECT * FROM REGEXP 'film_*' WHERE title='the film' does not work.
>>> The same is when I want to list all tables (to see what groups of
>>> films I have) - if I try do join SHOW TABLES with REGEXP I get an
>>> syntax error too. Where is the mistake and how to correct it?
>>>
>>> Thanks in advance
>>> Szymon Bobek
>>>
>>>
>> Your structure is fine... the problem is your method of searching. I'm
>> not really sure you can use REGEX in that context:
> You REALLY think that structure is fine???
>
>
What do you THINK is wrong with it? If you want to go for one big
table then your structure could become:
film_type set('horror','thriller','comedy','romance','action',...)
and then a row could be:
film_id film_title film_type
1 Romancing the Stone action, comedy, romance
2 GODZILLA action, sci-fi
3 Jurasic Park action, sci-fi
and your search(s) becomes:
SELECT * FROM film_table WHERE film_title LIKE '%Jurasic%'
returns data on Jurasic Park
or
SELECT * FROM film_table WHERE FIND_IN_SET('sci-fi')
returns data on GODZILLA and Jurasic Park
....does that look better. (an ENUM can contain only one item from a
list, a SET can contain multiple item from a list)
You would still want an index on the film_title. Not sure if you can
index a SET column but you could try.
Norm
Navigation:
[Reply to this message]
|