Group by not using index

[brainstech]

Hello,

I have 3 tables

tableA has info regarding name and clientid
tableB has info regarding the orderids of the clients
tableC has info regarding the tradeids of the clients
And all the tables have session column

primary key for tableA is (session,client id, name)
tableB has primary key(session, orderid) and index(session, clientid)
tableC has index(session, orderid)

and here is my query
[code=mysql]
select a.name, count(distinct a.clientid), sum(c.price)
from tableA a
STRAIGHT_JOIN tableB b on a.session=b.ses sion and a.clientid=b.cl ientid
STRAIGHT_JOIN tableC c use index (primary) on b.session=c.ses sion and b.orderid=c.ord erid
group by a.name
;
[/code]

the query is taking 45 seconds to execute
and extra field of the explain plan show using filesort for tableA

My question is how I have to modify the indexes to make the tableA to use the index so that query executes faster.

Thanks in advance

[Atli]

Hi.

What is the purpose of the "session" columns?

My guess would be that your Primary Keys are causing the delays.
Why do created a PK based on all three columns in table A?

As a general rule, you should never use multiple columns as a PK unless it is absolutely necessary.

A Primary Key should ideally be a row counter of sorts. 99% of the time that will do fine, and that will usually produce the fastest search results, especially when joining multiple tables.

Simply changing that in your tables, as well as adding Indexes for your "name", "session" and "price" columns should reduce the query times.

[coolsti]

Just to add a little to the previous reply:

what is the least common unique denominator in your table A? You say your primary key is composed of three columns: session, clientID and name, but why all three? Is the session column needed? And if so, is the name column needed?

In your query, you have the "on" condition for the table joins using the two columns "session" and "clientID", but not the "name" column. Because of this, mysql may not be able to use your primary key for your query. Therefore you may be able to increase speed by taking the "name" column out of your primary key in Table A. You may want to try that, and then see what the EXPLAIN results come up with.

Here I repeat your query:

select a.name, count(distinct a.clientid), sum(c.price)
from tableA a
STRAIGHT_JOIN tableB b on a.session=b.session and a.clientid=b.clientid
STRAIGHT_JOIN tableC c use index (primary) on b.session=c.session and b.orderid=c.orderid
group by a.name
;

I see another funny thing in the above: You have a "group by name", but could this also not be substituted by "group by clientid"? You have a count(distinct a.clientid), and looking at this I am becoming quite suspicious and curious about what you are really trying to do here. Your query and table structure may be a little bit "funny" for mysql to figure out. Try to post your "show create table" outputs here and also give a few words of explanation as to what session, clientid and name are supposed to mean here.