Make a view permanent

Question

I have a 8.6M table, with full text search but it is still impossible to use it. I can split this table in two, but I think there must be a better way to use it.

I tried to create a view, with temptable algorithms but it didn't create a physical table.

Table 1 - 8.6M rows

id    name    age
1     john    20
2     jean    25

View 1 - 200K rows - Only records where age = 25.

id    name    age
2     jean    25

@JohnRuddell the records must remain in the mail table. If I update the second one, should propagate to the first. — Erick Engelhardt, Nov 26 '14 at 18:37

score 2 · Answer 1 · answered Nov 26 '14 at 18:29

In MySQL, views are not "materialized views." Every time you query the view, it's like querying the base table. Some other RDBMS products have materialized views, where the subset of the table is also stored, but MySQL does not have this feature.

You have misunderstood the temptable algorithm for views. That means it creates a temporary table every time you query the view. This is probably not going to improve performance.

I'm not sure why you say that it's impossible to use the table. Do you mean that queries are not fast enough? That's not really the biggest table that MySQL can handle. There are tables that have hundreds of millions of rows and are still usable.

You may need different indexes to serve your query. You might benefit from partitioning (although I don't think table partitioning is compatible with fulltext indexes). You might need server hardware with more RAM or CPU horsepower.

If fulltext searches are important, you could also consider copying searchable data to a fulltext search technology like Sphinx Search. See my presentation Full Text Search Throwdown.

My queries are taking more then 50 seconds to select. It is a web application and must be faster. Thats why its impossible to use. Do you think it's better to split tables and create a procedure to update the main table whne the child gets updated? — Erick Engelhardt, Nov 26 '14 at 18:37

score 1 · Answer 2 · edited May 23 '17 at 10:26

if you want a view then create a VIEW like here: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

CREATE VIEW ....

If you want a temporary table then create a temporary table like here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

CREATE TEMPORARY TABLE ...

A view is permanent, but it is a view, not a table. Meaning that it will only execute a sql statement for you in the background each time you access it. Its purposes and role are not just to make your queries to look prettier but for example to be like a filter that will hide certain information.

A view will be accessible all the time. A temporary table has to be create within the DB connection cycle in order to be used.

Hopefully this answer will help you to decide what you really need - What to use? View or temporary Table

Make a view permanent

2 Answers2