Обсуждение: Optimize the query, help me please.

Поиск
Список
Период
Сортировка

Optimize the query, help me please.

От
Капралов Александр
Дата:
Query is:
SELECT * FROM a UNION SELECT * FROM b  ORDER BY time DESC LIMIT 100

how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b.

thanks.

Re: Optimize the query, help me please.

От
Raymond O'Donnell
Дата:
On 23/11/2011 10:56, Капралов Александр wrote:
> Query is:
> SELECT * FROM a UNION SELECT * FROM b  ORDER BY time DESC LIMIT 100
>
> how can i get only last 100 row from a and b and then do union. Explain
> of select said that all recond selected from a and b.

(select * from a order by time desc limit 100)
union
(select * from b order by time desc limit 100)
order by time desc limit 100;

BTW "time" is a reserved word AFAIK, so you should enclose it in
double-quotes or call the column something else.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Optimize the query, help me please.

От
Bèrto ëd Sèra
Дата:
Hi,

(SELECT * FROM a limit 10) union (SELECT * FROM b limit 10)

is what you need

Bèrto

2011/11/23 Капралов Александр <alnkapa@gmail.com>
Query is:
SELECT * FROM a UNION SELECT * FROM b  ORDER BY time DESC LIMIT 100

how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b.

thanks.



--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Re: Optimize the query, help me please.

От
Achilleas Mantzios
Дата:
Στις Wednesday 23 November 2011 12:56:23 ο/η Капралов Александр έγραψε:
> Query is:
> SELECT * FROM a UNION SELECT * FROM b  ORDER BY time DESC LIMIT 100
>
> how can i get only last 100 row from a and b and then do union. Explain of
> select said that all recond selected from a and b.
>

In order to get 100 last rows from a and 100 last rows from b do:
(SELECT * FROM a ORDER BY time LIMIT 100) UNION ALL (SELECT * FROM b ORDER BY time LIMIT 100);

omitting the "ALL" modifier, you may end up with less rows, since UNION normally returns only
distinct rows.

> thanks.
>



--
Achilleas Mantzios