MySQL Forums
Forum List  »  Performance

mysql query with modulus in where clause
Posted by: sreedevi iyer
Date: January 11, 2013 03:02PM

explain extended SELECT
*
FROM accountAge
WHERE (2567 -lastModifiedCalendarId) % 7 =0
and 2567 - lastUsedCalendarId < 7

Is there any where I can rewrite this Query. Right Now it is doing a tablescan

There is index on both the columns
lastModifiedCalendarId and lastUsedCalendarId.


Here is the table Structure:


create table accountAge (
accountId bigint(20) unsigned NOT NULL,
countryId smallint(5) unsigned NOT NULL,
lastModifiedCalendarId int NOT NULL,
lastUsedCalendarId int not null,
PRIMARY KEY (accountId),
index idx_lastModifiedCalendarId(lastModifiedCalendarId),
index idx_lastUsedCalendarId(lastUsedCalendarId)
) ENGINE=InnoDB DEFAULT CHARSET=ascii
;

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql query with modulus in where clause
3269
January 11, 2013 03:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.