Optimise Table Ideas?

[OhLordy]

+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+
| table                    | type   | possible_keys      | key               | key_len | ref                                 | rows | Extra       |
+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+
| new_products             | ALL    | price_code        | NULL              |    NULL | NULL                                | 5771 |             |
| w_fuzzy_search           | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |   19 |             |
| price_lookup             | eq_ref | pc1,currency_code | pc1               |      12 | new_products.price_code,const,const |    1 | Using where |
| price_codes              | eq_ref | PRIMARY           | PRIMARY           |       8 | new_products.price_code             |    1 | Using where |
| w_cosmetic_products      | ALL    | product_code      | NULL              |    NULL | NULL                                |    9 |             |
| w_cosmetic_series        | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_cosmetic_manufacturer  | eq_ref | PRIMARY           | PRIMARY           |       4 | w_cosmetic_series.cmref_id          |    1 |             |
| w_cosmetic_classes       | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_jewellery_products     | ALL    | product_code      | NULL              |    NULL | NULL                                |   15 |             |
| w_jewellery_series       | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_jewellery_manufacturer | eq_ref | PRIMARY           | PRIMARY           |       4 | w_jewellery_series.jmanref_id       |    1 |             |
| w_jewellery_classes      | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_material               | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_weight                 | eq_ref | PRIMARY           | PRIMARY           |       4 | w_jewellery_products.weight         |    1 |             |
| w_lingerie_products      | ALL    | product_code      | NULL              |    NULL | NULL                                |   12 | Using where |
| w_lingerie_series        | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_lingerie_manufacturer  | eq_ref | PRIMARY           | PRIMARY           |       4 | w_lingerie_series.lmref_id          |    1 |             |
| w_lingerie_classes       | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |    1 |             |
| w_colour                 | eq_ref | PRIMARY           | PRIMARY           |       4 | w_lingerie_products.colour          |    1 |             |
| product_type             | eq_ref | product_type      | product_type      |       1 | new_products.product_type           |    1 |             |
| w_search_keywords        | ref    | product_code_word | product_code_word |       4 | new_products.product_code           |    1 | Using where |
+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+

Any ideas on how I might be able to optimize this query? It's taking in excess of 10 secs in some cases
Thanks Rob

[tha_mink]

What query?

[jweissig]

hello,

i guess you did "EXPLAIN" and then your query. But we need to see the query so that we can check out what is going on. Can you also let us know if you have any indexes.

Here is what i want.

1. tables design's (example: "explain w_fuzzy_search"), this should include the indexes
2. the querys

thanks,
- justin

[OhLordy]

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| fuzzy_id | int(10)      |      | PRI | NULL    | auto_increment |
| keyword  | varchar(127) |      | UNI |         |                |
| a        | tinyint(4)   |      |     | 0       |                |
| b        | tinyint(4)   |      |     | 0       |                |
| c        | tinyint(4)   |      |     | 0       |                |
| d        | tinyint(4)   |      |     | 0       |                |
| e        | tinyint(4)   |      |     | 0       |                |
| f        | tinyint(4)   |      |     | 0       |                |
| g        | tinyint(4)   |      |     | 0       |                |
| h        | tinyint(4)   |      |     | 0       |                |
| i        | tinyint(4)   |      |     | 0       |                |
| j        | tinyint(4)   |      |     | 0       |                |
| k        | tinyint(4)   |      |     | 0       |                |
| l        | tinyint(4)   |      |     | 0       |                |
| m        | tinyint(4)   |      |     | 0       |                |
| n        | tinyint(4)   |      |     | 0       |                |
| o        | tinyint(4)   |      |     | 0       |                |
| p        | tinyint(4)   |      |     | 0       |                |
| q        | tinyint(4)   |      |     | 0       |                |
| r        | tinyint(4)   |      |     | 0       |                |
| s        | tinyint(4)   |      |     | 0       |                |
| t        | tinyint(4)   |      |     | 0       |                |
| u        | tinyint(4)   |      |     | 0       |                |
| v        | tinyint(4)   |      |     | 0       |                |
| w        | tinyint(4)   |      |     | 0       |                |
| x        | tinyint(4)   |      |     | 0       |                |
| y        | tinyint(4)   |      |     | 0       |                |
| z        | tinyint(4)   |      |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+

w_fuzzy_search

select new_products.product_code as product_code,
 new_products.product_type,
 product_type.short_name,
 price_codes.code_desc AS price_code_desc, 
w_cosmetic_products.product_code, 
w_jewellery_products.product_code, 
w_lingerie_products.product_code, 
price_lookup.price as price,
w_cosmetic_products.product_name as cosmetic_name, 
w_cosmetic_products.product_description as cosmetic_description, 
w_cosmetic_products.target_sex as cosmetic_target_sex, 
w_cosmetic_series.az_letter as cosmetic_series_az_letter, 
w_cosmetic_series.display_name as cosmetic_series_display_name, 
w_cosmetic_series.description as cosmetic_series_description, 
w_cosmetic_series.logo_image as cosmetic_series_logo_image, 
w_cosmetic_series.active as cosmetic_series_active, 
w_cosmetic_manufacturer.name as cosmetic_manufacturer_name, 
w_cosmetic_manufacturer.az_letter as cosmetic_manufacturer_az_letter, 
w_cosmetic_manufacturer.grouping as cosmetic_manufacturer_grouping, 
w_cosmetic_manufacturer.logo_image as cosmetic_manufacturer_logo_image,
 w_cosmetic_manufacturer.active as cosmetic_manufacturer_active, 
w_cosmetic_classes.display_name as cosmetic_classes_name, 
w_cosmetic_classes.description as cosmetic_classes_description, 
w_jewellery_products.product_name as jewellery_name, 
w_jewellery_products.product_description as jewellery_description, 
w_jewellery_products.target_sex as jewellery_target_sex, 
w_jewellery_products.age_group as jewellery_age_group, 
w_jewellery_products.material as jewellery_material, 
w_jewellery_products.weight as jewellery_weight, 
w_jewellery_products.size as jewellery_size, 
w_jewellery_series.az_letter as jewellery_series_az_letter, 
w_jewellery_series.display_name as jewellery_series_display_name, 
w_jewellery_series.description as jewellery_series_description, 
w_jewellery_series.logo_image as jewellery_series_logo_image, 
w_jewellery_series.active as jewellery_series_active, 
w_jewellery_manufacturer.name as jewellery_manufacturer_name, 
w_jewellery_manufacturer.az_letter as jewellery_manufacturer_az_letter, 
w_jewellery_manufacturer.grouping as jewellery_manufacturer_grouping, 
w_jewellery_manufacturer.logo_image as jewellery_manufacturer_logo_image, 
w_jewellery_manufacturer.active as jewellery_manufacturer_active, 
w_jewellery_classes.display_name as jewellery_classes_name, 
w_jewellery_classes.description as jewellery_classes_description, 
w_jewellery_classes.body_target as jewellery_classes_body_target, 
w_jewellery_classes.type as jewellery_classes_type, 
w_jewellery_classes.size as jewellery_classes_size, 
w_material.display_name as material_name, 
w_weight.display_name as weight_name, 
w_lingerie_products.product_name as lingerie_name, 
w_lingerie_products.product_description as lingerie_description, 
w_lingerie_products.colour as lingerie_colour, 
w_lingerie_products.size as lingerie_size, 
w_lingerie_series.az_letter as lingerie_series_az_letter, 
w_lingerie_series.display_name as lingerie_series_display_name, 
w_lingerie_series.description as lingerie_series_description, 
w_lingerie_series.logo_image as lingerie_series_logo_image, 
w_lingerie_series.active as lingerie_series_active, 
w_lingerie_manufacturer.name as lingerie_manufacturer_name, 
w_lingerie_manufacturer.az_letter as lingerie_manufacturer_az_letter, 
w_lingerie_manufacturer.grouping as lingerie_manufacturer_grouping, 
w_lingerie_manufacturer.logo_image as lingerie_manufacturer_logo_image, 
w_lingerie_manufacturer.active as lingerie_manufacturer_active, 
w_lingerie_classes.display_name as lingerie_classes_name, 
w_lingerie_classes.description as lingerie_classes_description, 
w_colour.display_name as colour_name, 
w_search_keywords.word as keyword,
 w_fuzzy_search.keyword as fuzz_word from new_products, 
price_codes, price_lookup, 
w_fuzzy_search left join w_cosmetic_products on new_products.product_code=w_cosmetic_products.product_code
 left join w_cosmetic_series on w_cosmetic_products.csref_id=w_cosmetic_series.csref_id
 left join w_cosmetic_manufacturer on w_cosmetic_manufacturer.cmref_id=w_cosmetic_series.cmref_id
 left join w_cosmetic_classes on w_cosmetic_classes.ccref_id=w_cosmetic_products.ccref_id
 left join w_jewellery_products on new_products.product_code=w_jewellery_products.product_code
 left join w_jewellery_series on w_jewellery_products.jsref_id=w_jewellery_series.jsref_id
 left join w_jewellery_manufacturer on w_jewellery_manufacturer.jmanref_id=w_jewellery_series.jmanref_id
 left join w_jewellery_classes on w_jewellery_classes.jcref_id=w_jewellery_products.jcref_id
 left join w_material on w_material.unique_id=w_jewellery_products.material
 left join w_weight on w_weight.unique_id=w_jewellery_products.weight
 left join w_lingerie_products on new_products.product_code=w_lingerie_products.product_code
 left join w_lingerie_series on w_lingerie_products.lsref_id=w_lingerie_series.lsref_id
 left join w_lingerie_manufacturer on w_lingerie_manufacturer.lmref_id=w_lingerie_series.lmref_id
 left join w_lingerie_classes on 
w_lingerie_classes.lcref_id=w_lingerie_products.lcref_id 
left join w_colour on w_colour.unique_id=w_lingerie_products.colour
 left join product_type on new_products.product_type=product_type.product_type
 left join w_search_keywords on new_products.product_code=w_search_keywords.product_code
 where price_codes.price_code=new_products.price_code
 && price_lookup.price_code=new_products.price_code
 && w_search_keywords.fuzzy_id=w_fuzzy_search.fuzzy_id
 && price_lookup.currency_code='GBP' 
&& price_lookup.price_code_2=1 && 
(w_cosmetic_products.product_code!='' || w_jewellery_products.product_code!='' || w_lingerie_products.product_code!='') and 
((w_search_keywords.metaphone='BRT' || w_search_keywords.soundex='B630' || w_search_keywords.word LIKE '%bart%' ) 
or ( ( LENGTH(w_fuzzy_search.keyword)>2 && LENGTH(w_fuzzy_search.keyword)<6 && w_fuzzy_search.b<=5 && w_fuzzy_search.a>=0 && w_fuzzy_search.r>=0 && w_fuzzy_search.t>=0) 
|| ( LENGTH(w_fuzzy_search.keyword)>2 && LENGTH(w_fuzzy_search.keyword)<6 && w_fuzzy_search.b>=0 && w_fuzzy_search.a<=5 && w_fuzzy_search.r>=0 && w_fuzzy_search.t>=0) 
|| ( LENGTH(w_fuzzy_search.keyword)>2 && LENGTH(w_fuzzy_search.keyword)<6 && w_fuzzy_search.b>=0 && w_fuzzy_search.a>=0 && w_fuzzy_search.r<=5 && w_fuzzy_search.t>=0) 
|| ( LENGTH(w_fuzzy_search.keyword)>2 && LENGTH(w_fuzzy_search.keyword)<6 && w_fuzzy_search.b>=0 && w_fuzzy_search.a>=0 && w_fuzzy_search.r>=0 && w_fuzzy_search.t<=5) 
|| ( w_fuzzy_search.b='1' && w_fuzzy_search.a='1' && w_fuzzy_search.r='1' && w_fuzzy_search.t='1' && LENGTH(w_fuzzy_search.keyword)>'2' && LENGTH(w_fuzzy_search.keyword)<'6')))

the query
HTH
Rob

[tsinka]

Can you post that field and key overview for all tables ?