Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Views in MySQL are not indexed so by their very nature require a full scan each time they are accessed. Generally speaking this makes Views really only useful for situations where you have a fairly complex static query that returns a small result set and you plan to grab the entire result set every time.</p> <p><b>Edit:</b> Of course Views will use the indexes on the underlying tables so that the View itself is optimized (otherwise they wouldn't make any sense at all to use) but because there are no indexes on a View it is not possible for a WHERE query on the View to be optimized. </p> <p>Constructing indexes for Views would be expensive anyway because while I've not tried to profile any Views, I'm fairly certain that a temp table is constructed behind the scenes and then the result set returned. It already takes plenty of time to construct the temp table, I wouldn't want a view that also tries to guess what indexes are needed. Which brings up the second point which is that MySQL does not currently offer a method to specify what indexes to use for a View so how does it know what fields need to be indexed? Does it guess based on your query? </p> <p>You might consider using a <a href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html" rel="noreferrer">Temporary Table</a> because then you can specify indexes on fields in the temporary table. However, from experience this tends to be really, really slow. </p> <p>If all this view contains is a SELECT ALL FROM table1, table2, table3; then I would have to ask why this query needs to be in a View at all? If for some reason its absolutely necessary, you might want to use a stored procedure to encapsulate the query as you'll then be able to get optimized performance while maintaining the benefit of a simpler call to the database for the result set.</p>
    singulars
    1. This table or related slice is empty.
    1. PODoes MySQL view always do full table scan?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNoah Goodrich
    1. USNoah Goodrich
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODoes MySQL view always do full table scan?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt says here http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html that the view will use the indexes of the underlying tables.
      singulars
      1. PO
      1. USAlex
    2. COI am pretty sure you are right - queries on MySQL views can use indexes from its source tables. You just can't have an index on the view itself and hence cannot have a single index containing columns from more than one of those tables.
      singulars
      1. PO
      1. USthomasrutter
    3. COUnfortunately that doesn't explain why that second query is so poorly optimised by MySQL. All I can think of is maybe that view isn't exactly joining in the way it is intended to. Don't know though.
      singulars
      1. PO
      1. USthomasrutter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload