Composite index

[meter]

I am having the following query and I am wondering how should I construct an index for each of the clients and products tables.

SELECT 
     clients.id AS id, 
     clients.name AS name, 
     COUNT(DISTINCT products.product_id) AS counts 
FROM 
     clients 
INNER JOIN 
     products ON (clients.id = products.client_id) 
WHERE 
     CAST("2008-07-30" AS DATE) BETWEEN products.start AND products.end AND 
     products.flag=0 AND 
     products.price<=products.value AND 
     products.price<=clients.balance 
GROUP BY 
     clients.id 
ORDER BY 
     clients.name ASC 
LIMIT 0, 100
 

Also, with all these Where, Inner join, Group by, Order by clauses, what order does a composite index apply to a query similar to the one above?

[coolsti]

I am having the following query and I am wondering how should I construct an index for each of the clients and products tables.

SELECT 
     clients.id AS id, 
     clients.name AS name, 
     COUNT(DISTINCT products.product_id) AS counts 
FROM 
     clients 
INNER JOIN 
     products ON (clients.id = products.client_id) 
WHERE 
     CAST("2008-07-30" AS DATE) BETWEEN products.start AND products.end AND 
     products.flag=0 AND 
     products.price<=products.value AND 
     products.price<=clients.balance 
GROUP BY 
     clients.id 
ORDER BY 
     clients.name ASC 
LIMIT 0, 100
 

Also, with all these Where, Inner join, Group by, Order by clauses, what order does a composite index apply to a query similar to the one above?

Try to run your query while prepending the word "Explain" to it. This will give you a table of information, that may help you decide what indexes will help best with our query. I assume that you have the products.product_id and clients.id as primary keys to their respective tables. If so, then these two are already indexed.

Basically, indexes help best for items that appear in where clauses, and group by clauses. So it might help to add an index on products.client_id. It may also help on some of the entities that appear in your where clause.

But I would use the EXPLAIN syntax to help me here, by adding indexes, seeing how that affects the EXPLAIN results, then maybe changing the indexes.

[akashazad]

Try to run your query while prepending the word "Explain" to it. This will give you a table of information, that may help you decide what indexes will help best with our query. I assume that you have the products.product_id and clients.id as primary keys to their respective tables. If so, then these two are already indexed.

Basically, indexes help best for items that appear in where clauses, and group by clauses. So it might help to add an index on products.client_id. It may also help on some of the entities that appear in your where clause.

But I would use the EXPLAIN syntax to help me here, by adding indexes, seeing how that affects the EXPLAIN results, then maybe changing the indexes.

Dude I just didn't got your "Explain" method can u just give it
with some example

[coolsti]

There is nothing really to Explain with the Explain. You just put it at the beginning of the query, and mysql then does not run the query, but gives you information about how many rows per table need to be examined. And which indexes maybe are used in the query.

It is up to you, then, to decide where adding extra indexes will help. At the worst case, you have a query where, let us say 5, tables are joined, and the Explain syntax tells you that ALL the rows of each table need to be examined. Expect then a long query time. At the best case, only one table at most needs all of its rows examined, maybe none of them do. This then will execute quickly.

I am sorry I have no time to give you examples. I explain to you what is in my head, but I do not have the time to sit at a console window and create something for you to see. Please refer to the Mysql documentation on the Explain keyword.