Mysql Query Efficiency

Question

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?

How many records are there in the table and do you have indexes on either of the fields you added to the query? — Braiba
– Braiba, Commented Jul 9, 2012 at 22:40
Isn't your second query should be SELECT * FROM tableName WHERE uid = id AND (status != "Deactive" OR Total != 0);. Also add indexes on these fields, your query will get executed faster... — Fahim Parkar
– Fahim Parkar, Commented Jul 9, 2012 at 22:40
There are only a couple thousand records. And no I do not have any indexes on these attributes since the table isn't very large. — ewein
– ewein, Commented Jul 9, 2012 at 22:49

JoeCortopassi · Accepted Answer · 2012-07-09 22:57:32Z

2

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

edited Jul 9, 2012 at 22:57

answered Jul 9, 2012 at 22:42

JoeCortopassi

5,0939 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Trahey Over a year ago

I think if asker adds the appropriate parenthesis, then the same performance will be had without the subquery (MySQL will very quickly discard rows that do not match). As it is (without the parens), the row cannot be eliminated until Total is evaluated, since the OR condition may include it.

Scanmaster · Accepted Answer · 2012-07-09 22:56:55Z

-1

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;

answered Jul 9, 2012 at 22:56

Scanmaster

1002 silver badges9 bronze badges

1 Comment

ewein Over a year ago

No, this query will remove all rows that have a status of Deactive or a total of 0. I still want rows with a status of Deactive as long as the total is not 0.

Collectives™ on Stack Overflow

Mysql Query Efficiency

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related