CODEWITHSUNDEEP
mysqlsqldatabase
sirait
sirait

Reputation: 125

Faster group by in mysql?

I have a table like this

|customer_reference|date_and_time|
       111          2013-01-01
       111          2013-01-02
       111          2013-01-03
       222          2013-01-01
       222          2013-01-02
       333          2013-01-01

I want to get row with last date_and_time according to customer_reference. I've written a few queries but it is very slow. My query like that.

SELECT MAX(date_and_time) AS time
FROM t_table GROUP BY customer_reference

I have more that 600.000 row in that table.

Upvotes: 1

Views: 3228

Answers (3)

Vishal Suthar
Vishal Suthar

Reputation: 17194

Indexes are a feature that you can enable on your MySQL tables to increase performance.

You have to create an index on column customer_reference Maximizing Query Performance through Column Indexing in MySQL

If we have not defined the index at creation time, we could always add one later, using the following syntax:

CREATE INDEX id_index ON t_table(customer_reference);

Upvotes: 1

echo_Me
echo_Me

Reputation: 37253

its better if you add an index to your table

    ALTER TABLE `t_table` ADD INDEX `customer_reference` (`customer_reference`)

and then run your query

Upvotes: 1

John Woo
John Woo

Reputation: 263803

just add index on column customer_reference and execute the query below

SELECT customer_reference, MAX(date_and_time) AS time
FROM t_table 
GROUP BY customer_reference

Upvotes: 2

Related Questions