Which is faster for a search - a table or a view?

Databases
1

Hi,

I have a VIEW in mysql that is created from 4 different unrelated tables with a UNION

there is over 25000 records in the view. It was created solely for the purpose of doing a search. There are 4 columns and 2 are the main ones for the searching.

It is rather slow, so I was thinking of converting the view to a table as the view will never change - it is in an old database that serves the searches of archived records only.

Will converting the VIEW to a TABLE speed up the search?

If so, how do I do the conversion?

Thank you for your time.

2

You only when you try :wink:

No, in all seriousness it should be faster because then the DBMS is able to index properly (you do have indexes, right?)

Off the top of my head INSERT INTO newTable SELECT * FROM myView should do the trick of creating a table from the view :slight_smile:

3

Off the top of my head INSERT INTO newTable SELECT * FROM myView should do the trick of creating a table from the view

Your head is better than any page I found when trying to test this - they all were giving the same syntax and it was wrong so I could not even try! :smiley:

However, because you gave me this I can now try as you suggested and report back that searching a large table is faster than searching a VIEW

Thank you soo much for your help - it is appreciated :smiley:

4

You’re welcome :slight_smile: How about those indexes? :wink:

5

I have 4 cols in the table and 2 have numbers (ID’s) so I put the indexes on them - the other 2 cols are text and long text - can an index go on those also? I think that index is just for numbers but correct me if I am wrong :smiley:

6

You can index text and long text in FULLTEXT indexes if you like, but you should only index them if you also search them. If you don’t, then don’t bother :slight_smile: