faster SELECT statement

#1

Hello !

i have one big table with measurement data consisting of a timestamp and
a station id and 3 value columns:

to get an info about the newest data i developed this SQL:

SELECT max( tstamp ), stat FROM mw0000 WHERE 1 GROUP BY stat ORDER BY
stat ASC LIMIT 64

it takes a little to much time:
+----------------+------+

Quote:

max( tstamp ) | stat |
+----------------+------+
20031103232000 | GFZ1 |
20040415170818 | GFZ2 |
20040415170841 | TEST |
+----------------+------+

3 rows in set (4.41 sec)

how can i enhance this to be faster ???

i have
+----------+

Quote:

count(*) |
+----------+
849946 |
+----------+

many rows in the table. i have this indexes:
mysql> show index from mw0000;
+--------+------------+---------------+--------------+-------------+-----------+-------------+--------
--+--------+---------+

Quote:

t | Packed | Comment |
+--------+------------+---------------+--------------+-------------+-----------+-------------+--------
--+--------+---------+

Quote:

mw0000 | 0 | key_unique | 1 | tstamp | A
NULL | NUL
L | NULL | |
mw0000 | 0 | key_unique | 2 | stat | A
NULL | NUL
L | NULL | |
mw0000 | 0 | key_unique | 3 | comp | A
849946 | NUL
L | NULL | |
mw0000 | 1 | tstamp | 1 | tstamp | A
121420 | NUL
L | NULL | |
mw0000 | 1 | stat | 1 | stat | A
3 | NUL
L | NULL | |
mw0000 | 1 | comp | 1 | comp | A
18 | NUL
L | NULL | |
mw0000 | 1 | key_time_stat | 1 | tstamp | A
121420 | NUL
L | NULL | |
mw0000 | 1 | key_time_stat | 2 | stat | A
121420 | NUL
L | NULL | |

+--------+------------+---------------+--------------+-------------+-----------+-------------+--------
--+--------+---------+

Or do i need a faster machine ??

thanks peter

#2

In the last exciting episode, hans-peter demus <demus (AT) t-online (DOT) de> wrote:

Quote:

i have one big table with measurement data consisting of a timestamp
and a station id and 3 value columns:

to get an info about the newest data i developed this SQL:

SELECT max( tstamp ), stat FROM mw0000 WHERE 1 GROUP BY stat ORDER BY
stat ASC LIMIT 64

it takes a little to much time:
+----------------+------+
| max( tstamp ) | stat |
+----------------+------+
| 20031103232000 | GFZ1 |
| 20040415170818 | GFZ2 |
| 20040415170841 | TEST |
+----------------+------+
3 rows in set (4.41 sec)

how can i enhance this to be faster ???

i have
+----------+
| count(*) |
+----------+
| 849946 |
+----------+
many rows in the table.

Well, this query inherently has got to scan the entire table, so the
cost you are seeing is the cost of examining every single entry in the
table. The timing is not overly surprising.

If you have an index on stat and tstamp thus:

create index stat_tstamp on mw0000 (stat, tstam);

And you have a comprehensive list of all of the STAT values, then you
could issue a series of queries thus:

select stat, tstamp from mw0000 where stat = 'GFZ1' order by tstamp desc limit 1;
select stat, tstamp from mw0000 where stat = 'GFZ2' order by tstamp desc limit 1;
select stat, tstamp from mw0000 where stat = 'TEST' order by tstamp desc limit 1;
... and so forth ...

Of course, if you don't know in advance the population of STAT values,
you'll have to scan the table to get them, which will probably take
about 4.41 seconds...
--
"cbbrowne","@","cbbrowne.com"
http://www3.sympatico.ca/cbbrowne/languages.html
As of next Thursday, ITS will be flushed in favor of TOPS-10.
Please update your programs.

#3

Quote:

I have one big table with measurement data consisting of a
timestamp, station id and 3 value columns:

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

The code you posted is not SQL it is not even close to SQL! What
mutant package are you using?

Quote:

Or do I need a faster machine ??

You probably need better software.

#4

Christopher Browne schrieb:

Quote:

In the last exciting episode, hans-peter demus <demus (AT) t-online (DOT) de> wrote:

i have one big table with measurement data consisting of a timestamp
and a station id and 3 value columns:

to get an info about the newest data i developed this SQL:

SELECT max( tstamp ), stat FROM mw0000 WHERE 1 GROUP BY stat ORDER BY
stat ASC LIMIT 64

it takes a little to much time:
+----------------+------+
| max( tstamp ) | stat |
+----------------+------+
| 20031103232000 | GFZ1 |
| 20040415170818 | GFZ2 |
| 20040415170841 | TEST |
+----------------+------+
3 rows in set (4.41 sec)

how can i enhance this to be faster ???

i have
+----------+
| count(*) |
+----------+
| 849946 |
+----------+
many rows in the table.

Well, this query inherently has got to scan the entire table, so the
cost you are seeing is the cost of examining every single entry in the
table. The timing is not overly surprising.
okay i was wondering if MYSQL was building up a temporary cross-over

table [tstamp X stat]. so this should not be the problem.

Quote:

If you have an index on stat and tstamp thus:

create index stat_tstamp on mw0000 (stat, tstam);
Now i do have this index also

And you have a comprehensive list of all of the STAT values, then you
could issue a series of queries thus:

select stat, tstamp from mw0000 where stat = 'GFZ1' order by tstamp desc limit 1;
select stat, tstamp from mw0000 where stat = 'GFZ2' order by tstamp desc limit 1;
select stat, tstamp from mw0000 where stat = 'TEST' order by tstamp desc limit 1;
.. and so forth ...
takes 8.5 secs for the first and then 0.0 secs for the other ones.....

Of course, if you don't know in advance the population of STAT values,
you'll have to scan the table to get them, which will probably take
about 4.41 seconds...

no i have other tables for definition of used values for "STAT".

thanks a lot for Your answer

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

faster SELECT statement

comp.databases comp.databases

faster SELECT statement - 04-19-2004 , 08:42 AM

Re: faster SELECT statement - 04-19-2004 , 04:15 PM

Re: faster SELECT statement - 04-19-2004 , 06:05 PM

Re: faster SELECT statement - 04-23-2004 , 09:02 AM