Re: Optimize a query

[Linus Pettersson]

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