Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

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