CODEWITHSUNDEEP
mysql
ewein
ewein

Reputation: 2735

Mysql Query Efficiency

I have a mysql table of data and I need to only return the rows that do not have a status of "Deactive" and do not have a Total of 0 (but there can be rows where status is deactive and total is not 0 and vice versa). Before I needed this requirement I was just doing the standard query to select all rows:

SELECT * FROM tableName WHERE uid = id;

However now when I change the query to add the constraints above:

SELECT * FROM tableName WHERE uid = id AND (status != "Deactive" OR Total != 0);

This bottom query is taking much much longer to complete. Why is this happening and is there a better way I can do this query?

Upvotes: 1

Views: 82

Answers (2)

JoeCortopassi
JoeCortopassi

Reputation: 5093

The first query is looking up based on an index (I'm assuming by 'uid'). The second query is filtering on other values. Run this, and it will help you figure out how you can best optimize it:

EXPLAIN EXTENDED SELECT * FROM tableName WHERE uid = id AND status != "Deactive" OR Total != 0;

It's dirty, but this would probably be a quick way to speed it up:

SELECT 
  *
FROM 
(
  SELECT 
    * 
  FROM 
    tableName 
  WHERE 
    uid = id 
) as temp
WHERE
  temp.status != "Deactive" 
  OR temp.Total != 0;

This would force the query to just get the rows with a matching uid first, and then filter them down, instead of having to do a big table scan. Like I said before though, EXPLAIN EXTENDED is your friend

Upvotes: 2

Scanmaster
Scanmaster

Reputation: 100

Do you need to return all the data in each row? ie picking only the columns you need will make the query run faster.

You also say you need to 'return the rows that do not have a status of "Deactive" and do not have a Total of 0'. Your query should then read:

SELECT * (or column names) FROM tableName WHERE uid = id AND status != "Deactive" AND Total != 0;

Upvotes: -1

Related Questions