CODEWITHSUNDEEP
phpmysqlsql
sandilya
sandilya

Reputation: 53

if else clause in sql, automatic rebuild of indexes

I am in need of a sql construct which gives the following functionality.

select if-expression if status='regular' else else-expression 
from table-name ;

This operation will be used very frequently. So, I am considering building an index for this operation.

But, I heard that indexes are not rebuilt after table is being updated. Is there a way we can have automatically rebuild indexes?

Thanks in advance

Upvotes: 1

Views: 32

Answers (2)

umlcat
umlcat

Reputation: 4143

You did not provide a more detailed example.

There are several alternative solutions to your idea, not just the "use-the-index-luke-solution". And depends a lot on your database.

Another alternative could be using "union". In some circumstances could use too much resources, in others, may be the optimal solution, even if using too many records.

SELECT
  <if-expression>
FROM
  MyTable
WHERE
  (MyTable.status = 'regular')

UNION

SELECT
  <else-expression>
FROM
  MyTable
WHERE
  (MyTable.status <> 'regular')

And, in some circumstances, you may also add

SELECT
  <if-expression>
FROM
  MyTable
WHERE
  (MyTable.status = 'regular')
ORDER BY <indexed-fields-used-in-expression> 


UNION

SELECT
  <else-expression>
FROM
  MyTable
WHERE
  (MyTable.status <> 'regular')
ORDER BY <indexed-fields-used-in-expression>

Cheers.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The translation in SQL of your statement is:

select (case when status = 'regular' then <if-expression> else <else-expression> end)
from tablename;

An index will not help with this query, because you are not limiting the rows in any way. An index can help when you have filters in a where clause, joins, and correlated subqueries (and sometimes I think with group by).

And as MarcB points out in a comment, MySQL (and all other databases) keep indexes up to date for insert, update, and delete operations.

Upvotes: 1

Related Questions