Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon

Links

XODOX
Impressum

#1: query efficiency

Posted on 2011-09-27 14:32:30 by supr_star

--1355153491-2091569531-1317126750=:38612
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Here's a simple query: select name,status,min(dt), max(dt),count(*)=0A=
from mytable=0Agroup by name I need an efficient way to get this data,=
along with the status of the row with the max(dt). =A0'status' is not in t=
he group by, so I get apparently random statuses. =A0Is there any way to do=
this without a table join? =A0Or I guess the real question is: =A0What is =
the most efficient way of accomplishing this? =A0The table is huge, 25M row=
s or so, but I can create any indexes.
--1355153491-2091569531-1317126750=:38612--

Report this message

#2: Re: query efficiency

Posted on 2011-09-27 19:47:22 by (Halász Sándor) hsv

>>>> 2011/09/27 05:32 -0700, supr_star >>>>
Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this?
<<<<<<<<
I cannot answer the real question, but there is this:

select name,(SELECT status
FROM mytable AS x
WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status,
min(dt), max(dt),count(*) from mytable group by name

I will not vouch that this is more efficient than joining.

(Surely there are enough of this that this is entitled to a special SQL construct.)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Report this message