Hi Ivan,
Am Samstag, den 03.12.2005, 09:49 +0100 schrieb PaginaDeSpud:
> Hi, Ingo, maybe you didn't received my last email above:
maybe you didn't receive my reply from Mon, 28 Nov 2005 12:12:44 +0100.
I sent it to PaginaDeSpud <webmaster@stripped> only, not to the
list. here is what I wrote:
sorry for the late reply. I just fetched your mail from below a big
stack. :(
Am Mittwoch, den 09.11.2005, 23:15 +0100 schrieb PaginaDeSpud:
> Hi Ingo,
>
> here are the table settings, and the query.
> Yes, i use myisam, could it be possible with innodb ?
No, I don't think so.
> Thanks for your attention ;)
>
> CREATE TABLE `yabbse_members` (
> `ID_MEMBER` int(10) unsigned NOT NULL auto_increment,
I missed the auto_increment in your first email to the list. :(
This makes things much different. You want to fetch the last fifteen
rows. The LIMIT clause does not directly support a fetch of the last
values. Hence, you try to order the rows descending and fetch the first
fifteen. Unfortunately, the auto_increment always grow and rows are
appended at the end of the file. With this approach you cannot win.
...
> SELECT v.`votos`, v.`votos_totales`, v.`media`, v.`esvip`,
y.`ID_MEMBER`,
> y.`memberName`, y.`realName`, y.`suspendido`, y.`dateRegistered`,
> y.`avatar`, y.`foto_genteya`, y.`versionfoto`, y.`veces_retiradas`,
> y.`ID_MEMBER`, y.`nombre`, y.`birthdate`, y.`pais`, y.`cod_ciudad`,
> y.`ciudad`, y.`gender` FROM `votos_totales` v LEFT JOIN
`yabbse_members` y
> ON (v.`id`=y.`ID_MEMBER`) WHERE 1 AND NOT y.`suspendido` AND
y.`tiene_foto`
> AND y.`gender` LIKE 'chico' ORDER BY y.`ID_MEMBER` DESC LIMIT 0,15
There are two things, you can try.
- Order the table ascending (with ALTER TABLE). Never delete rows or
re-order immediately afterwards. Before the above select, select the
highest ID_MEMBER, subtract 15 and use the result for the first number
of LIMIT. You might need to read lock the table around the two selects.
- Define the primary key as descending. It should then be used for the
ORDER BY clause.
Unfortunately, I have not enough experience with these SQL things. The
first method sounds quite complicated, but should work. The second
method sounds elegant, but might fail if for some reason MySQL does not
use the DESC key for ORDER BY in this case.
Regards,
Ingo
--
Ingo Strüwing, Senior Software Developer
MySQL AB, www.mysql.com
