Complex query/index optimisation

Hi

I am new to this forum, but I can see many strongheads here. Since over a year I fight with my software provider, which cannot really solve the issue with a killer query.

Hope one of you, with a brand new insight into the story will find some sort of solution. Let me go into details:

Killer query is:

With EXPLAIN it brings following results:

By that I can see that real problem is indexation of (re) 18brating_entries table. Let me give you insight into this:

Anybody has any idea how to correct query/indexation to make it fly?


Thanks in advance for any comments and idea exchange.


Tom Zlos

 

Almost forgotten

Environment: MySQL 4.1.16 over PHP 4.3.11 and Apache 2.0.55
Hardware: cluster of 3 webs with a multiple CPU's mysql machine behind

 

1. Try to use left join first ...
2. Look at mysql configuration file and try to optimize that (mysql have by default some conf files for loaded sites or big databases).

Try my query and tell me how is working :

SELECT AVG(re.rating_value) AS avgscore, r. * , r.date_added AS date_review_added, i. * , v. *
FROM 18breviews AS r left join 18bitems AS i on r.item_id = i.item_id left join 18bvendors AS v on v.vendor_id = i.vendor_id left join 18brating_entries AS re  on re.review_id = r.review_id WHERE i.active =1 AND r.active =1
GROUP BY r.review_id
ORDER BY r.date_added DESC
LIMIT 5

Code (markup):

I make a simple change on your table columns: I supposed rebvew_id is from table 18breviews

Best Regards
Adrian
http://www.mydomaintracker.net

 

First trial:

but let's have a look on the second run of the query (query cache is amazing thing )

Assuming: 2nd run is fine, but the first one is the real pain... Especially due to the fact, that database changes 10 times per minute and query cache is not helping then...


Regarding mysql configuration, what would you change there knowing I am running 2x 2.8GHz Xeon with 2GB RAM?

Here comes my my.cnf file:

Regards
Tom

 

Mysql have some predifined files for small/medium/large/huge mysql servers.

This values are from there:

set-variable = key_buffer=384M
set-variable = max_allowed_packet=32M
set-variable = table_cache=512
set-variable = join_buffer=16M
set-variable = sort_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = range_alloc_block=3072
set-variable = tmp_table_size=64M
set-variable = record_buffer=16M
set-variable = thread_cache=384
set-variable = thread_concurency=4

 

How can we optimize this query when we only know the structure of one table?

We need to check the indexes as well as the relationships.

 

I impossible to make the query fly if you know only 1 table from all ... but you dont have access at this database ? or ?

Regards
Adrian