Hi, Ingo, maybe you didn't received my last email above:
Hi Ingo,
here are the table settings, and the query.
Yes, i use myisam, could it be possible with innodb ?
Thanks for your attention ;)
CREATE TABLE `yabbse_members` (
`ID_MEMBER` int(10) unsigned NOT NULL auto_increment,
`memberName` varchar(40) NOT NULL default '',
`realName` varchar(20) default NULL,
`passwd` tinytext NOT NULL,
`emailAddress` tinytext,
`dateRegistered` bigint(20) default NULL,
`suspendido` tinyint(1) unsigned NOT NULL default '0',
`comentarios_bloqueados` int(1) unsigned NOT NULL default '0',
`comentarios_veces_bloqueados` int(3) unsigned NOT NULL default '0',
`comentarios_fecha_ultimo_bloqueo` int(9) unsigned NOT NULL default '0',
`personalText` tinytext,
`memberGroup` tinytext,
`gender` tinytext,
`birthdate` date NOT NULL default '0000-00-00',
`hideEmail` tinyint(4) default '1',
`timeFormat` tinytext,
`signature` text,
`posts` int(11) default NULL,
`timeOffset` float default NULL,
`avatar` tinytext,
`foto_genteya` tinytext NOT NULL,
`versionfoto` bigint(20) NOT NULL default '0',
`tiene_foto` tinyint(1) unsigned NOT NULL default '0',
`foto_protegida` int(1) unsigned NOT NULL default '0',
`veces_retiradas` int(3) unsigned NOT NULL default '0',
`im_ignore_list` text,
`im_email_notify` tinyint(4) NOT NULL default '0',
`lastLogin` bigint(20) default NULL,
`karmaBad` int(11) NOT NULL default '0',
`karmaGood` int(11) NOT NULL default '0',
`usertitle` tinytext,
`lngfile` tinytext,
`notifyAnnouncements` tinyint(4) NOT NULL default '1',
`notifyOnce` tinyint(4) NOT NULL default '1',
`MSN` tinytext,
`memberIP` tinytext,
`secretQuestion` tinytext NOT NULL,
`secretAnswer` tinytext NOT NULL,
`nombre` varchar(30) NOT NULL default '',
`cod_ciudad` int(4) unsigned NOT NULL default '0',
`ciudad` varchar(30) NOT NULL default '',
`cp` varchar(10) NOT NULL default '',
`pais` int(3) NOT NULL default '0',
`aficiones` varchar(255) NOT NULL default '',
`ocupacion` varchar(100) NOT NULL default '',
`horario` varchar(100) NOT NULL default '',
`notas` varchar(255) NOT NULL default '',
`estado` varchar(50) NOT NULL default '',
`ref` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`ID_MEMBER`),
KEY `dateRegistered` (`dateRegistered`),
KEY `memberGroup` (`memberGroup`(30)),
KEY `emailAddress` (`emailAddress`(50)),
KEY `suspendido` (`suspendido`),
KEY `tiene_foto` (`tiene_foto`),
KEY `pais` (`pais`,`ID_MEMBER`),
KEY `memberName` (`memberName`),
KEY `realName` (`realName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `votos_totales` (
`id` int(10) unsigned NOT NULL default '0',
`votos` int(10) NOT NULL default '5',
`votos_totales` int(10) NOT NULL default '5',
`media` float NOT NULL default '0',
`esvip` int(1) NOT NULL default '0',
`veces_vip` int(4) unsigned NOT NULL default '0',
`sms` int(3) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `veces_vip` (`veces_vip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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
----- Original Message -----
From: "Ingo Strüwing" <ingo@stripped>
To: <internals@stripped>
Sent: Wednesday, November 09, 2005 10:47 AM
Subject: Re: ALTER TABLE `mytable` ORDER BY `key_field` DESC
> Hi,
>
> Am Mittwoch, den 09.11.2005, 10:12 +0100 schrieb PaginaDeSpud:
>> Hi,
>> ALTER TABLE `mytable` ORDER BY `key_field` DESC
>>
>> Why if i set the default order by of my table, being key_field PRIMARY
>> KEY
>> AUTOINCREMENT, it only makes an initial order by but all rows inserted
>> later
>> are not ordered?
>
> If you have PRIMARY KEY AUTOINCREMENT and never delete any row, the
> order of the rows should be preserved. If you need to delete rows, write
> lock the table, delte the rows, optimize the table, and unlock it.
>
>> Is it too much difficult to add these new rows in this order?
>
> If we tried this, we would have to move the file contents above the new
> row up by the length of the row. In the average this means copying half
> of the data file for each insert. We surely won't do that. (I assumed
> you use the MyISAM storage engine.)
>
>>
>> My site makes thousands of querys asking for the last 15 rows inserted on
>> this table, ordered by primary key desc, over 200.000 rows and it takes
>> 0.68
>> seconds on a dual XEON (4 processors), but it takes 0.08 seconds if no
>> order
>> is asked. It would be beatiful if you consider this option on future
>> versions of mysql.
>
> Do you use a LIMIT clause?
> Anyway, for an educated guess it would be helpful to see the table
> definition and the query.
>
> Regards,
> Ingo
> --
> Ingo Strüwing, Senior Software Developer
> MySQL AB, www.mysql.com
> Office: +49 30 43672407
>
> Are you MySQL certified? www.mysql.com/certification
>
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?unsub=1
>
>
