simple queries taking long time to run

[Tinnin]

Hi everyone,

I have about 20 simple queries running seperately on loading a php page with mysql querying. On my localhost the page takes about 3 seconds to load but on the live server this is over 30 seconds. I'm trying to shave some time off but I'm having trouble figuring out exactly why it is so slow. Even on my localhost 3 seconds seems a bit long for just a few simple queries.

The database I am selecting from has about 100,000 rows of which I'm selecting for the most part just one field from. It uses a MyISAM engine.

An example of where it slows up:
I am running two similar queries, one without a conditional and one with. The first:

SELECT COUNT(*) AS sales FROM Master

takes about 0.0003 second to run on my localhost. When I add a conditional:

SELECT  COUNT(*) AS sales FROM Master
WHERE YEARWEEK(SaleDate) = YEARWEEK(DATE_SUB(CURDATE(),INTERVAL 2 WEEK)) 

it takes about 0.2 seconds on my localhost.

In the last query I am trying to get the number of sales from 2 weeks ago. The week number is a dynamic variable from php.

Is there a better way to write the last query and speed things up?

[cereal]

Hi,

first problem is CURDATE(), the query won't be cached, you could try to send this date from PHP and see if it improves the execution, at least you should see a difference when the query is repeated on the same range.

[Tinnin]

Thanks for your reply cereal.

I gave your suggestion a go but the speed remained the same.

Each query is only run once. The page is for a sales person to see their stats for this week, last week, etc regarding number of sales and retention rates and all that sort of thing. There are only about 20 stats shown on the page but for some reason it is causing the page to take a long time to load. Especially when live.

ANy chance you could show your script?

[cereal]

Also: what kind of column type are you using for SaleDate? Is this column nullable? You could see a performance difference if using date, datetime or varchar and by setting it to not null, for example:

CREATE INDEX sales ON Master(SaleDate);

-- on
CREATE TABLE `Master` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `product` text NOT NULL,
  `price` varchar(100) NOT NULL,
  `saled_at` datetime NOT NULL, -- format YYYY-mm-dd H:i:s
  `SaleDate` date NOT NULL,     -- format YYYY-mm-dd
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

I made few tests with datetime and date, and also a previous test with varchar, and by using the date type, with NOT NULL and with an index, it should speed up a bit.

[Taywin]

Your YEARWEEK() function is the problem. This post should answer what you need.