Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Please share your CREATE TABLE statement with us. –  GhostGambler Mar 27 at 8:52
    
I have updated my question... –  rakibtg Mar 27 at 9:22

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thankyou for directing me to the right path :) –  rakibtg Mar 27 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.