[RESOLVED] Why LIMIT doesn't work in some cases?

Zpixel

mysql_query("SELECT * FROM table1 ORDER BY id DESC LIMIT 5");

the query above must return the newest 5 records stored in database.

id-----field1---- field2
1-----data1-----data2
2-----data1-----data2
3-----data1-----data2
4-----data1-----data2
5-----data1-----data2
6-----data1-----data2

id is mediumint(9) and has been indexed in db structure.
i expect to see record 6 in output but it doesn't occure, so i was compeld to revise the code:

$i=0;
mysql_query("SELECT* FROM table1 ORDER BY id DESC");
while($row= .........) {
if ($i==5) break;
$i++;
....
..
}

in another table, when the 'id' column type is text and hasn't been indexed, the limit parameter works fine.
mysql_query("SELECT * FROM table2 ORDER BY id DESC LIMIT 5");

should i always choose the type as text and not to index the column to be able to use the LIMIT parameter properly?

coldwerturkey

Zpixel;10910312 wrote:
mysql_query("SELECT * FROM table1 ORDER BY id DESC LIMIT 5");

the query above must return the newest 5 records stored in database.

id-----field1---- field2
1-----data1-----data2
2-----data1-----data2
3-----data1-----data2
4-----data1-----data2
5-----data1-----data2
6-----data1-----data2

id is mediumint(9) and has been indexed in db structure.
i expect to see record 6 in output but it doesn't occure,

Maybe my inexperience over simplifies complex questions and I'm missing something but; LIMIT 5 produces 5 results. Not 6. If you want 6, set to LIMIT 6.

bradgrafelman

Not entirely sure what the problem is. I created a table with the same structure and data as you posted above, and got this:

SQL query: SELECT * FROM `test` ORDER BY id DESC LIMIT 5;
Rows: 5
id 	field1 	field2
6 	data1 	data2
5 	data1 	data2
4 	data1 	data2
3 	data1 	data2
2 	data1 	data2

Is that not what you got and/or expected?

Zpixel

real table

CREATE TABLE table1 (
feed_id int(11) NOT NULL auto_increment,
custumer_id text NOT NULL,
categories_id smallint(6) NOT NULL default '0',
feed_name text NOT NULL,
feed_description text NOT NULL,
feed_link text NOT NULL,
feed_content text NOT NULL,
update_time int(11) NOT NULL default '0',
register_date text NOT NULL,
KEY feed_id (feed_id),
KEY categories_id (categories_id),
KEY update_time (update_time)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

real query:

$result = mysql_query("SELECT * FROM table1 where($condition) ORDER BY update_time LIMIT 10");

Zpixel

do auto_increment columns need to be indexed or it is useless?
i've changed auto_increment columns from 'index' to 'primary'. is it good?

bradgrafelman

Since the auto_increment is going to be unique, it's commonly used for the primary key.

As for your query, were you expecting the most recent 10 results? If so, you didn't add the 'DESC' keyword to your ORDER BY.

Zpixel

thanks.