MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize a query
Posted by: Linus Pettersson
Date: March 15, 2011 02:20PM

Hi again, and thank you for your time.

Show table status gives me this:

+-----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name                        | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| jos_clothes_brands          | MyISAM |      10 | Dynamic    |   691 |             58 |       40704 |   281474976710655 |         9216 |         0 |            693 | 2011-03-09 01:47:05 | 2011-03-14 23:11:27 | 2011-03-09 01:51:26 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_categories      | MyISAM |      10 | Dynamic    |    59 |             53 |        3160 |   281474976710655 |         7168 |         0 |             68 | 2011-03-09 01:47:21 | 2011-03-10 21:44:45 | 2011-03-09 01:51:38 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_catraws         | MyISAM |      10 | Dynamic    |  1296 |             54 |       70292 |   281474976710655 |        47104 |         0 |           1297 | 2011-03-10 11:19:59 | 2011-03-14 22:20:19 | 2011-03-10 11:19:59 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_favorites       | MyISAM |      10 | Dynamic    |     1 |            112 |         112 |   281474976710655 |         2048 |         0 |           NULL | 2011-03-09 13:07:42 | 2011-03-09 13:07:42 | NULL                | utf8_general_ci |     NULL |                |         | 
| jos_clothes_images          | MyISAM |      10 | Dynamic    | 49764 |            380 |    18953700 |   281474976710655 |      1022976 |         0 |          49767 | 2011-03-09 01:48:18 | 2011-03-14 23:14:37 | 2011-03-09 01:52:08 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_imports         | MyISAM |      10 | Fixed      |    40 |             37 |        1480 | 10414574138294271 |         2048 |         0 |             41 | 2011-03-09 01:48:33 | 2011-03-14 22:16:18 | 2011-03-09 01:52:23 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_products        | MyISAM |      10 | Dynamic    | 24610 |            582 |    14338848 |   281474976710655 |      1998848 |         0 |          24619 | 2011-03-14 22:31:29 | 2011-03-15 21:11:04 | 2011-03-14 22:31:48 | utf8_general_ci |     NULL |                |         | 
| jos_clothes_reports         | MyISAM |      10 | Dynamic    |     0 |              0 |           0 |   281474976710655 |         1024 |         0 |              1 | 2011-03-09 01:49:32 | 2011-03-09 01:49:32 | NULL                | utf8_general_ci |     NULL |                |         | 
| jos_clothes_resellers       | MyISAM |      10 | Dynamic    |     6 |            200 |        1200 |   281474976710655 |         2048 |         0 |              7 | 2011-03-09 13:27:07 | 2011-03-09 13:27:07 | NULL                | utf8_general_ci |     NULL |                |         | 
| jos_clothes_view_brands     | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
| jos_clothes_view_categories | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
| jos_clothes_view_imports    | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
| jos_clothes_view_products   | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
| jos_clothes_view_reports    | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
| jos_clothes_view_resellers  | NULL   |    NULL | NULL       |  NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | VIEW    | 
+-----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
15 rows in set (0.06 sec)



Regarding the SELECT that creates the VIEW it returns all the rows in the products table.


I have now increased my key buffer to 50M too.

Best Regards
Linus

Options: ReplyQuote


Subject
Views
Written By
Posted
2866
March 10, 2011 08:59AM
1399
March 13, 2011 03:48PM
1473
March 14, 2011 03:35PM
1484
March 14, 2011 06:26PM
Re: Optimize a query
1336
March 15, 2011 02:20PM
1377
March 15, 2011 09:12PM
1279
March 28, 2011 06:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.