MySQL Where Range and Grouping

Databases
1

To the gurus at SitePoint (incl. r937) :):

I have used MySQL for a very long time but haven’t ever figured out a solution for this query problem that I have. Suppose I have a query as such:


SELECT
	column_x,
FROM
	table
WHERE
	column_a = ?
	and column_b >= ?
GROUP BY
	column_c

The index is on column_a, column_b, column_c. This unfortunately will yield a temporary table and a filesort.

So I changed the code and added ORDER BY null; at the end of the clause. That gets rid of filesort but still uses a temporary table to get the results.

Finally, I have re-created my index to the following: column_a, column_c, column_b. This doesn’t use a filesort and temporary table, but I now believe MySQL is going into the table and not using the index to grab the results of column_b range. (Correct me if I’m wrong).

My question - how can I create this query by using only indexes? Is it possible? I haven’t found a solution yet but hoping someone can guide me.

2

try an index on ( c, b, a )

:wink:

3

I recreated the index to be on c, b, a but that also gives filesort + temporary table. Here is the select explain:

+----+-------------+------------------------+------+---------------+------+---------+------+------+------------------------------------
----------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra
          |
+----+-------------+------------------------+------+---------------+------+---------+------+------+------------------------------------
----------+
|  1 | SIMPLE      | some_table | ALL  | NULL          | NULL | NULL    | NULL | 2367 | Using where; Using temporary; Using
 filesort |
+----+-------------+------------------------+------+---------------+------+---------+------+------+------------------------------------
----------+
4

oh well, that’s me done my guesses

say, why do you have GROUP BY anyway? you aren’t doing any aggregation

5

Guesses? Never seen you guess before. Why start in this thread? :stuck_out_tongue:

The columns actually do have sum(), I just didn’t place it in the example code.

Do you think MySQL is capable of accomplishing what I am trying to do? It would all be nice and dandy if only the WHERE clause didn’t have a range. As soon there’s a range it doesn’t use the index for the GROUP BY unless the first column of the index is the same column that the data is being grouped by. (Or grouped by all columns of the index).

What I want to do here is use a range on one column, and group by another. And seems there’s no way to get MySQL to use an index in this case.

But then again, I am outdated when it comes to this. So maybe something I have missed or something new. I am using 5.0.