Mysql – Fetching User Recent Status

database-designexecution-planmyisamMySQL

I am running mysql with MyISAM storage engine to store user status(user updates data).
To load user recent updates data i am using this query:

$limit = 20;
$uid = 3;
SELECT * FROM userstatus WHERE uid = {$uid} ORDER BY id DESC LIMIT {$limit}

but is the query is performance efficient, because i am ordering it descending by the status ID (which is in ascending order)?
SQL command to create the Table:

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL,
  statustype CHAR (16) NOT NULL,
  PRIMARY KEY (id)
);

So how the query will work? Is it go through and fetch all the posts of an user then order it DESC using the status ID and finally limit it to 20?
is it ok, or any better way to query it?
Thanks

Best Answer

You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan.

You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort.

Use this create table statement

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL DEFAULT 0,
  statustype CHAR (16) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX (`uid`, `id`)
);

and see this and your query in action in this sqlfiddle.

If you are interested in being capable to do similar improvements on your own, you should start by learning to read the output of EXPLAIN.

Related Question