CODEWITHSUNDEEP
mysqlview
Erick Engelhardt
Erick Engelhardt

Reputation: 734

Make a view permanent

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

Upvotes: 1

Views: 2808

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562388

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.

Upvotes: 2

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

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

Upvotes: 1

Related Questions