emotn — 2012-05-10T06:44:44-04:00 — #1
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.
scallioxtx — 2012-05-10T07:44:04-04:00 — #2
You only when you try
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
emotn — 2012-05-10T22:18:09-04:00 — #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!
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
scallioxtx — 2012-05-11T02:14:55-04:00 — #4
You're welcome How about those indexes?
emotn — 2012-05-11T09:45:54-04:00 — #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
scallioxtx — 2012-05-11T11:32:19-04:00 — #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