Optimizing ENORMOUS MySQL View [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Does MySQL view always do full table scan?

Running SELECT * FROM vAtom LIMIT 10 never returns (I aborted it after 48 hours);

explain select * from vAtom limit 10 :

+----+-------------+---------------+--------+-------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------+-----------+---------------------------------+
| id | select_type | table         | type   | possible_keys                             | key           | key_len | ref                                                                                            | rows      | Extra                           |
+----+-------------+---------------+--------+-------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------+-----------+---------------------------------+
|  1 | SIMPLE      | A             | ALL    | primary_index,atom_site_i_3,atom_site_i_4 | NULL          | NULL    | NULL                                                                                           | 571294166 | Using temporary; Using filesort | 
|  1 | SIMPLE      | S             | ref    | primary_index                             | primary_index | 12      | PDB.A.Structure_ID                                                                             |         1 | Using index                     | 
|  1 | SIMPLE      | C             | eq_ref | PRIMARY,chain_i_1,sid_type,detailed_type  | PRIMARY       | 24      | PDB.A.Structure_ID,PDB.A.auth_asym_id                                                          |         1 | Using where                     | 
|  1 | SIMPLE      | AT            | eq_ref | primary_index                             | primary_index | 24      | PDB.A.Structure_ID,PDB.A.type_symbol                                                           |         1 | Using index                     | 
|  1 | SIMPLE      | entityResidue | 开发者_开发百科ref    | PRIMARY                                   | PRIMARY       | 52      | PDB.S.Structure_ID,PDB.A.label_entity_id,PDB.A.label_seq_id,PDB.A.label_comp_id,PDB.C.Chain_ID |         1 | Using where; Using index        | 
|  1 | SIMPLE      | E             | ref    | primary_index                             | primary_index | 12      | PDB.AT.Structure_ID                                                                            |         1 | Using where                     | 
+----+-------------+---------------+--------+-------------------------------------------+---------------+---------+------------------------------------------------------------------------------------------------+-----------+---------------------------------+
6 rows in set (0.00 sec)

You don't have to tell me that 600M rows is a lot. What I want to know is why it's slow when I only want 10 rows, and what can I do from here.

I'll be glad to post show create for anything per requests (don't want to make this post 7 pages long)

---

Tables can have a built-in sort order, this default kicks in on any query where you don't specify your own sorting. So your query is still trying to sort those 570+ million rows so it can find the first 10.

---

I'm not really surprised. Consider the case where you are simply joining 2 tables A and B and are limiting the result set; it may be that only the last N rows from table A have matching, then the database would have to go through all the rows in 'A' to get the N matching rows.

This would unavoidably be the case if there are lots of rows in 'B'.

You'd like to think that it would work the other way around when there are only a few rows in B - but obviously that's not the case here. Indeed, IIRC LIMIT has no influence on the generation of a query plan - even if it did, mysql does not seem to cope with push-predicates for views.

If you provided details of the underlying tables, the number of rows in each and the view it should be possible to write a query referencing the tables directly which runs a lot more efficiently. Alternatively depending on how the view is used, you may be able to get the desired behaviour using hints.

---

It claims to be using a filesort. The view must have an ORDER BY or DISTINCT on an unindexed value, or the index is not specific enough to help.

To fix it, either change the view so that it does not need to sort, or change the underlying tables so that they have an index that will make the sort fast.

---

I think show create would be useful. It looks like you have a full table scan on vAtom. Maybe if you put an ORDER BY clause, after an indexed field, it would perform better.