rownum equivalent unter MySQL?

Hallo,

ich habe ein kleines Problemchen. Ich moechte gerne aus einer Tabelle die
letzten 5 IDs zurueckbekommen die kleiner sind als die angegebene.
Realisiert habe ich das jetzt mit:

mysql> SELECT id FROM params WHERE id<30000 order by id desc limit 0,5;
+-------+
| id |
+-------+
| 29999 |
| 29998 |
| 29997 |
| 29996 |
| 29995 |
+-------+
5 rows in set (0.39 sec)

Das ist mir zu langsam. DESC verraet einen filesort aufgrund des desc. Das
ist schlecht ;)

Unter Oracle wuerde ich sowas in der Art wie

SELECT id FROM params WHERE id<30000 and rownum between 30000-5 and 30000;

machen. Da koennte ich mir den sort also sparen. Kann ich das irgendwie in
MySQL realisieren? (Achtung rownum != id - wenn ich id-5 rechne habe ich
ein Problem wenn mein id Feld Luecken aufweist).

--
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/

Re: rownum equivalent unter MySQL?

Oliver Lehmann wrote:

> Unter Oracle wuerde ich sowas in der Art wie
>
> SELECT id FROM params WHERE id<30000 and rownum between 30000-5 and
> 30000;
>
> machen. Da koennte ich mir den sort also sparen. Kann ich das irgendwie
> in MySQL realisieren? (Achtung rownum != id - wenn ich id-5 rechne habe
> ich ein Problem wenn mein id Feld Luecken aufweist).

SELECT id FROM params WHERE id<30000 limit 29995,5;

:) koennte mann die 30000-5 auch noch on-the-fly in mysql rechnen?
Ansonsten muss ich das in der Sprache drumrum machen...


--
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/

Re: rownum equivalent unter MySQL?

mysql> select id from params where id<100000 order by id asc limit
99998,1;
+-------+
| id |
+-------+
| 99999 |
+-------+
1 row in set (0.91 sec)

irgendwie dauert mir das zu lange.... sieht da noch einer einen weg zur
Optimierung?


mysql> desc params;
+----------+---------------------+------+-----+---------+--- ----+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+--- ----+
| id | bigint(20) unsigned | | PRI | 0 | |
| filename | varchar(255) | YES | | NULL | |
| width | int(11) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
| type | varchar(255) | YES | | NULL | |
| comment | varchar(255) | YES | | NULL | |
+----------+---------------------+------+-----+---------+--- ----+


--
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/

Re: rownum equivalent unter MySQL?

Oliver Lehmann wrote:
> mysql> select id from params where id<100000 order by id asc limit
> 99998,1;

Ich kann mich täuschen aber wenn die absteigend sortierst und dann den
ersten Datensatz selectest müsste es schon schneller sein.

--
Fachbegriffe der Informatik 100: Hacker
»Zwanghafte Programmierer, die nur für das Programm leben und
deshalb auf ihre Körperpflege verzichten.« (Joseph Weizenbaum,
"Die Macht der Computer und die Ohnmacht der Vernunft")

Re: rownum equivalent unter MySQL?

Oliver Lehmann wrote:
> Oliver Lehmann wrote:

>> Ich moechte gerne aus einer Tabelle die
>> letzten 5 IDs zurueckbekommen die kleiner sind als die angegebene.

>> mysql> SELECT id FROM params WHERE id<30000 order by id desc limit 0,5;

So macht man das korrekt. Ein Index auf 'id' beschleunigt diese Query
übrigens sehr erfolgreich.

>> Unter Oracle wuerde ich sowas in der Art machen
>> SELECT id FROM params WHERE id<30000 and rownum between 30000-5 and 30000;
....
> SELECT id FROM params WHERE id<30000 limit 29995,5;

Ohne ORDER BY gibts keine Garantie für die Reihenfolge der Datensätze.

Das LIMIT schlägt fehl, wenn es Lücken gibt (sagen wir mal, es gibt nur
10 id's < 30000). Die Oracle-Lösung vermutlich auch (wenn 'rownum' das
ist, was ich vermute).


XL

Re: rownum equivalent unter MySQL?

Axel Schwenke wrote:

> Oliver Lehmann wrote:
> > Oliver Lehmann wrote:
>=20
> >> Ich moechte gerne aus einer Tabelle die
> >> letzten 5 IDs zurueckbekommen die kleiner sind als die angegebene.
>=20
> >> mysql> SELECT id FROM params WHERE id<30000 order by id desc limit
> >0,5;
>=20
> So macht man das korrekt. Ein Index auf 'id' beschleunigt diese Query
> übrigens sehr erfolgreich.

Nunja, nicht wirklich. Dadurch das die Column primary ist, hab ich da eh
schon einen Index drauf. Und irgendwo wirds mir halt zu langsam :( da ich
3 Ergebnisse brauch (id-1, id-10, id-100) Leider faellt mir da jetzt auch
nix ein wie man das in ein select packen koennte. Was ich im Grunde mache:

select id,filename from params where id<100000 order by id desc limit 0,1
select id,filename from params where id<100000 order by id desc limit 10,1
select id,filename from params where id<100000 order by id desc limit
100,1

um jeweils das letzte, das zehntletzte und das hundertletzte element zu
bekommen. Auf filename ist ein index. Problem ist halt die lange Laufzeit
des ganzen.

mysql> select id,filename from params where id<100000 order by id desc
limit 0,1;
+-------+------------------+
| id | filename |
+-------+------------------+
| 99999 | 1132356763422jpg |
+-------+------------------+
1 row in set (2.46 sec)

das ganze jetzt min 3 mal.... online etwas zu lang :(

mysql> desc select id,filename from params where id<100000 order by id
desc limit 0,1;
+--------+-------+---------------+------+---------+------+-- ------+
| table | type | possible_keys | key | key_len | ref | rows |
+--------+-------+---------------+------+---------+------+-- ------+
| params | range | PRIMARY,id | id | 8 | NULL | 160403 |
+--------+-------+---------------+------+---------+------+-- ------+
+-----------------------------------------+
| Extra |
+-----------------------------------------+
| where used; Using index; Using filesort |
+-----------------------------------------+

Dazu kommt dann nochmal ein Select der mir die fuenfletzten Eintraege
liefert.

--=20
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/

Re: rownum equivalent unter MySQL?

Wie wärs mit einer Art Oracle Snapshot, den du immer wenn nötig neu
erstellst, in den nur die benötigten Zeile stehen.

FGB

--
Fachbegriffe der Informatik 100: Hacker
»Zwanghafte Programmierer, die nur für das Programm leben und
deshalb auf ihre Körperpflege verzichten.« (Joseph Weizenbaum,
"Die Macht der Computer und die Ohnmacht der Vernunft")

Re: rownum equivalent unter MySQL?

Oliver Lehmann wrote:
> Axel Schwenke wrote:
>
>> Oliver Lehmann wrote:
>> > Oliver Lehmann wrote:
>>
>> >> Ich moechte gerne aus einer Tabelle die
>> >> letzten 5 IDs zurueckbekommen die kleiner sind als die angegebene.
>>
>> >> mysql> SELECT id FROM params WHERE id<30000 order by id desc limit
>> >0,5;
>>
>> So macht man das korrekt. Ein Index auf 'id' beschleunigt diese Query
>> übrigens sehr erfolgreich.
>
> Nunja, nicht wirklich. Dadurch das die Column primary ist, hab ich da eh
> schon einen Index drauf. Und irgendwo wirds mir halt zu langsam :(

Definiere langsam! Bei mir ist das nicht langsam:

| idefix:~ # mysql test
| Your MySQL connection id is 9395 to server version: 4.1.7
|
| mysql> create table test (id int unsigned not null auto_increment primary key,
| -> string varchar(255));
| Query OK, 0 rows affected (0.01 sec)
|
|
| idefix:~ # cat test.pl
| use DBI;
| use Digest::MD5;
|
| my $n = shift or die;
|
| my $md5 = Digest::MD5->new;
| my $dbh = DBI->connect("DBI:mysql:database=test") or die;
| my $sth = $dbh->prepare("INSERT INTO test (string) VALUES (?)") or die;
|
| print scalar localtime(), "\n";
| while ($n--) {
| $md5->add($n);
| $sth->execute($md5->hexdigest);
| }
| print scalar localtime(), "\n";
|
|
| idefix:~ # perl test.pl 100000
| Wed Dec 8 00:50:00 2004
| Wed Dec 8 00:50:45 2004
|
|
| mysql> select count(*) from test;
| +----------+
| | count(*) |
| +----------+
| | 100000 |
| +----------+
| 1 row in set (0.00 sec)
|
| mysql> select * from test where id<12345 order by id desc limit 0,1;
| +-------+----------------------------------+
| | id | string |
| +-------+----------------------------------+
| | 12344 | 2de68a7c5e3db6a836ab8f5f109416b8 |
| +-------+----------------------------------+
| 1 row in set (0.00 sec)
|
| mysql> select * from test where id<12345 order by id desc limit 10,1;
| +-------+----------------------------------+
| | id | string |
| +-------+----------------------------------+
| | 12334 | 3400a7cc4c590b9f55f55c5863e1707a |
| +-------+----------------------------------+
| 1 row in set (0.01 sec)
|
| mysql> select * from test where id<12345 order by id desc limit 100,1;
| +-------+----------------------------------+
| | id | string |
| +-------+----------------------------------+
| | 12244 | d69cb2286b19b7e24a984a667977f0a4 |
| +-------+----------------------------------+
| 1 row in set (0.00 sec)
|
| mysql> select * from test where id<1234 order by id desc limit 0,1;
| +------+----------------------------------+
| | id | string |
| +------+----------------------------------+
| | 1233 | 4eb269bdf2d3fb361eb837c219d26eac |
| +------+----------------------------------+
| 1 row in set (0.01 sec)
|
| mysql> select * from test where id<1234 order by id desc limit 10,1;
| +------+----------------------------------+
| | id | string |
| +------+----------------------------------+
| | 1223 | 06ee4842cff75cdd5cd353d33226fede |
| +------+----------------------------------+
| 1 row in set (0.00 sec)
|
| mysql> select * from test where id<1234 order by id desc limit 100,1;
| +------+----------------------------------+
| | id | string |
| +------+----------------------------------+
| | 1133 | fa7332c871281211f2a3d4410fa97893 |
| +------+----------------------------------+
| 1 row in set (0.00 sec)
|
|
| idefix:~ # cat /proc/cpuinfo | head -8 | tail -4
| model name : Pentium III (Katmai)
| stepping : 3
| cpu MHz : 501.148
| cache size : 512 KB
|
| idefix:~ # free
| total used free shared buffers cached
| Mem: 515852 502356 13496 0 118700 53824
| -/+ buffers/cache: 329832 186020
| Swap: 256024 64408 191616


-> obwohl die Kiste eher schwachbrüstig ist, laufen alle Queries
innerhalb einer Zeitscheibe von 10ms ab. Wahrscheinlich schneller.

Besonders bemerkenswert finde ich die Geschwindigkeit beim INSERT.
100.000 rows in 45 Sekunden macht sagenhafte 2222 rows/sec. Das MySQL
ist nicht besonders getuned; ist halt meine private Spielwiese.


ABER

> mysql> desc select id,filename from params where id<100000 order by id
> desc limit 0,1;
> +--------+-------+---------------+------+---------+------+-- ------+
>| table | type | possible_keys | key | key_len | ref | rows |
> +--------+-------+---------------+------+---------+------+-- ------+
>| params | range | PRIMARY,id | id | 8 | NULL | 160403 |
> +--------+-------+---------------+------+---------+------+-- ------+
> +-----------------------------------------+
>| Extra |
> +-----------------------------------------+
>| where used; Using index; Using filesort |
> +-----------------------------------------+

Hier ist was merkwürdig. Wieso gibt es zwei Indizes? Warum steht da
"Using index" *und* "Using filesort"? Steinalte MySQL-Version?


XL

Re: rownum equivalent unter MySQL?

Axel Schwenke wrote:


> |
> | idefix:~ # cat /proc/cpuinfo | head -8 | tail -4
> | model name : Pentium III (Katmai)
> | stepping : 3
> | cpu MHz : 501.148
> | cache size : 512 KB
> |
> | idefix:~ # free
> | total used free shared buffers =20
> | cached
> | Mem: 515852 502356 13496 0 118700 =20
> | 53824-/+ buffers/cache: 329832 186020
> | Swap: 256024 64408 191616
>=20
>=20
CPU: Pentium II/Pentium II Xeon/Celeron (334.09-MHz 686-class CPU)
Origin =3D "GenuineIntel" Id =3D 0x652 Stepping =3D 2
=20
Features=3D0x183fbff CA,CMOV,PAT,PSE36,MMX,FXSR>
real memory =3D 268435456 (256 MB)
avail memory =3D 257089536 (245 MB)
FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
cpu0 (BSP): APIC ID: 0
cpu1 (AP): APIC ID: 1



> Hier ist was merkwürdig. Wieso gibt es zwei Indizes? Warum steht da
> "Using index" *und* "Using filesort"? Steinalte MySQL-Version?

weil ich noch einen id,filename index angelegt habe. MySQL ist 3.23.58
Werde mir mal ne 4.1er installieren.



--=20
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/

Re: rownum equivalent unter MySQL?

Oliver Lehmann wrote:

> CPU: Pentium II/Pentium II Xeon/Celeron (334.09-MHz 686-class CPU)
> Origin =3D "GenuineIntel" Id =3D 0x652 Stepping =3D 2
> =20
> Features=3D0x183fbff > ,M CA,CMOV,PAT,PSE36,MMX,FXSR>
> real memory =3D 268435456 (256 MB)
> avail memory =3D 257089536 (245 MB)
> FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
> cpu0 (BSP): APIC ID: 0
> cpu1 (AP): APIC ID: 1
>=20
>=20
>=20
> > Hier ist was merkwürdig. Wieso gibt es zwei Indizes? Warum steht da
> > "Using index" *und* "Using filesort"? Steinalte MySQL-Version?
>=20
> weil ich noch einen id,filename index angelegt habe. MySQL ist 3.23.58
> Werde mir mal ne 4.1er installieren.

ok.. mit 4.1 ist das ein "witz" ;)

mysql> select id from params where id<100000 order by id desc limit 100,1;
+-------+
| id |
+-------+
| 99899 |
+-------+
1 row in set (0.00 sec)


--=20
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/