3

I added a new index in my table and now phpMyAdmin is sorting the rows by that column by default. How do I make phpMyAdmin sort the rows by the id column instead of the url column by default?

CREATE TABLE IF NOT EXISTS `links` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `url` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=343959 ;
Leo Jiang
  • 24,497
  • 49
  • 154
  • 284

2 Answers2

1

There is no "default" ordering of rows. If you want a query to return rows in a particular order, then you must use an order by clause.

You cannot even depend on rows being returned by the primary key order. In fact, with updates/deletes/inserts on the table, this will often not be true.

If you want a query to return rows in a particular order, then you must use an order by clause (I realize I repeated that). The only exception is that MySQL (in violation of the standard) guarantees the ordering of results when using group by.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This might be a feature of phpMyAdmin then? Before today, phpMyAdmin added `order by id` by default. After adding the index, it now adds `order by url` – Leo Jiang Jul 11 '13 at 01:14
  • phpMyAdmin does add an `ORDER BY` clause by default, and this actually creates headaches when clicking on the table tree view; we have to kill the query on the server when a very large dataset is hit with that "feature". – Yanick Rochon Oct 20 '21 at 14:09
1

You can resolve this issue by adding default order by to the tableusing the alter table query.

QUERY: alter table links order by id;

If you not specify any order by clause in where clause, then rows will sort by default "id" column.

Amernath
  • 360
  • 1
  • 5
  • 1
    Tables do not have default order. Read MySQL's documentation about ALTER TABLE to see what this statement does - and which cases it works. – ypercubeᵀᴹ Feb 02 '17 at 21:39