Preserve order string field in Mysql 1

[sen5241b]

I have a list of place names in a text file ordered by how well known the place is. I want to load the list into a simple one field table in Mysql and preserve the order in which it is loaded. When I list out the table it is listed alphabetically. Using PHP, how can I preserve the original order without adding a numbering field?

 

[jpadie]

you should add an autoincrement primary key to the mysql table. then when you insert values sequentially into the table from the text file, a placeholder value is also added automatically.

 

[sen5241b]

Good idea -thanks. I will assume that the answer is really NO. MySql has no way to preserve the original non-alphabetized insertion order for a list of strings.

 

[jpadie]

sure there is.

but it will involve removing the index from the table. i imagine that you have made your word field either a primary key or unique index. innoDB and myISAM will sort on these indexes if they exist.

i think adding a throw-away autoincrementing primary key feels like an easier solution that still leaves you the ability to index the word column with a unique index.

 

[ChrisHirst]

And what is your query for retrieving the data?

Because if you simply insert the values they will be in "natural order" and unles you deliberately change that be returned in exztcly the same order.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[jpadie]

@ChrisHirst

that is not the case. If there is a primary or unique index on the table then, in the absence of an express ORDER BY clause, the recordset will always be returned ordered by that index.

 

[ingresman]

Perhaps add a rank column beside the place name (something must have decided the order at some time) and order by that rank when you need to.
A general design rule is never use side effects in data, for example the index may change or even be droped. Try to explicity get your data on the order you need.

 

[sen5241b]

The autoincrement key field worked just fine and was fairly simple. When listing out the table, without any specific sort order, the original load order was preserved.

 

[ingresman]

Justin:
i would have thought that if no order is specified the strorage engine would just read the table serially rather than read the index and then key into the table. On a large table with an untidy index this would lead to more i/o than really needed. I think to assume an order will lead to a fragile query. If you have a reference i'd be interested to see it and i'll shut up!

 

[jpadie]

@ingresman

I did find a reference but it was not precisely on point. however try this script to test the scenario (changing your connect as needed of course)

<?php
mysql_connect(  ':/Applications/MAMP/tmp/mysql/mysql.sock',$u, $p);

mysql_query('create database if not exists wordTest');
mysql_select_db('wordTest');
mysql_query('create table if not exists test (word varchar(255) primary key)') or die(mysql_error());
mysql_query('create table if not exists test2 (word varchar(255))') or die(mysql_error());

foreach(array('banana','mango','apple') as $fruit):
	foreach(array('test', 'test2') as $table):
		@mysql_query("insert into $table (word) values('$fruit')");
	endforeach;
endforeach;

foreach(array('test', 'test2') as $table):
	echo "<hr/>Table $table <br/>";
	$result = mysql_query("select word from $table") or die(mysql_error());
	while($row = mysql_fetch_assoc($result)):
		echo $row['word'] . '<br/>';
	endwhile;
endforeach;

 

[ChrisHirst]

@ChrisHirst

that is not the case. If there is a primary or unique index on the table then, in the absence of an express ORDER BY clause, the recordset will always be returned ordered by that index.

There are TWO IFs in that statements which is at least one assumption too many.


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[jpadie]

@chrisHurst

re-read the chain.
had an index not been present on the single column, the order would have been as entered.
since it was not we can safely assume that an index was present.

the point was that your assertion was (taken baldly or in context) not correct and therefore should not be relied upon by future readers :

unles you deliberately change that be returned in exztcly the same order.

adding an unique or primary index would not necessarily be considered a deliberate change of recordset order

 

[sen5241b]

CREATE TABLE test ( original_order INT UNSIGNED NOT NULL AUTO_INCREMENT, somestring VARCHAR(25) NOT NULL UNIQUE,  PRIMARY KEY(original_order) )

A simple select with no sort order specified list somestring out in the order it was loaded. This answered my original question.

 

[jpadie]

@sen5241b

yes that works because you are specifying the primary key on the original_order field and thus the dbms is implicitly sorting on that field. that is why i suggested that solution at the start ;-)

 

[ingresman]

I had a go and my hat goes off to you!
I still think implicit ordering is bad though !

 

[jpadie]

I still think implicit ordering is bad though !

agreed. this is not a well publicised feature of unique indices (although I think it is common across other carrier class databases). Always better to rely on simple things like numeric fields ....