CODEWITHSUNDEEP
mysql
Yan.Zero
Yan.Zero

Reputation: 449

MySQL:How to optimize this SQL?

select  max(id)
from pt_l_program
where starttime<=UNIX_TIMESTAMP() 
group by lid order by null

Here is the result of explain :

| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra |
|:---|:------------|:-------|:-----|:--------------|:----|:--------|:----|:-----|:------|
| 1  | SIMPLE      | program| ALL  | StartTime     | null| null    | null| 99999| Using where; Using temporary|

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You want an index on the table. One option is pt_l_program(starttime, lid, id).

However, it might be better to write the query without a group by at the outer level. Let me assume that you have a table with all the "l" values:

select (select max(p.id)
        from pt_l_program p
        where starttime <= UNIX_TIMESTAMP() and p.l_id = l.l_id
       )
from l

The above index is needed for this query as well.

Upvotes: 1

Related Questions