Optimizing ENORMOUS MySQL View

Question

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)

Might be related: http://stackoverflow.com/questions/637328/does-mysql-view-always-do-full-table-scan — Zan Lynx, Jun 24 '11 at 15:12

Zan Lynx · Answer 1 · 2011-06-24T18:17:51.750

1

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.

edited Jun 24 '11 at 18:17

answered Jun 24 '11 at 15:10

Zan Lynx

53,022
10
79
131

When you say "it must have" -- are you saying it does and hence it's slow, or that it should be put in there? – Mikhail Jun 24 '11 at 15:47

score 1 · Answer 2 · answered Jun 24 '11 at 15:11

1

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.

answered Jun 24 '11 at 15:11

Marc B

356,200
43
426
500

I half understand this. Though it's slow when I don't specify a limit... – Mikhail Jun 24 '11 at 15:47

score 1 · Answer 3 · answered Jun 24 '11 at 15:53

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.

Writing the entire query would work (I think i've tested that already). Adding a `where` also works (returns 1000 results in 0.03 s). Please elaborate on "using hints" — Mikhail, Jun 24 '11 at 16:05

score 0 · Answer 4 · answered Jun 24 '11 at 15:10

0

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.

answered Jun 24 '11 at 15:10

Tudor Constantin

26,330
7
49
72

You can't have an index in a view :( – Mikhail Jun 24 '11 at 15:45

Optimizing ENORMOUS MySQL View

4 Answers4