SQL Queries too slow (Need Expert Advise)

I have this code(provided below) that it takes too slow to get the specific data of the following dates

last 7 days, 10 days, 15 days, 30 days, 3 months, 6 months, year to date, 1 year, 2 yrs, 3 yrs, 4 yrs, 5yrs, 10 yrs, 15 yrs, 20 yrs and the first entry of the record.

Can you help me to optimize it?
Any help is highly appreciated.

$d7 = array('from' => date("Ymd",strtotime("-7 days")), 'to' => date('Ymd'));    #7days
        $d10 = array('from' => date("Ymd",strtotime("-10 days")), 'to' => date('Ymd')); #10 days
        $d15 = array('from' => date("Ymd",strtotime("-15 days")), 'to' => date('Ymd')); #15 days
        $d20 = array('from' => date("Ymd",strtotime("-20 days")), 'to' => date('Ymd')); #20 days
        $day30 = array('from' => date("Ymd",strtotime("-30 days")), 'to' => date('Ymd')); #30 days
        $m3 = array('from' => date("Ymd",strtotime("-3 months")), 'to' => date("Ymd",strtotime("-2 months"))); #3 months
        $m6 = array('from' => date("Ymd",strtotime("-6 months")), 'to' => date("Ymd",strtotime("-5 months"))); #6 months
        $ytd = array('from' => date("Y").'0101', 'to' => date('Ymd')); #year to date
        $y1 = array('from' => date("Ymd",strtotime("-12 months")), 'to' => date("Ymd",strtotime("-11 months"))); #1 year
        $y2 = array('from' => date("Ymd",strtotime("-24 months")), 'to' => date("Ymd",strtotime("-23 months"))); #2 years
        $y3 = array('from' => date("Ymd",strtotime("-36 months")), 'to' => date("Ymd",strtotime("-35 months"))); #3 years
        $y4 = array('from' => date("Ymd",strtotime("-48 months")), 'to' => date("Ymd",strtotime("-47 months"))); #4 years
        $y5 = array('from' => date("Ymd",strtotime("-60 months")), 'to' => date("Ymd",strtotime("-59 months"))); #5 years
        $y10 = array('from' => date("Ymd",strtotime("-120 months")), 'to' => date("Ymd",strtotime("-119 months"))); #10 years
        $y15 = array('from' => date("Ymd",strtotime("-180 months")), 'to' => date("Ymd",strtotime("-179 months"))); #15 years
        $y20 = array('from' => date("Ymd",strtotime("-240 months")), 'to' => date("Ymd",strtotime("-239 months"))); #20 years
            
        $sql = "(select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #7 days
                   " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #10 days
                   " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #15 days
                   " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #20 days
                   " union all                
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #30 days
                   " union all
                   (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #3 months
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #6 months
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #ytd
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y1
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y2
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y3
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y4
                " union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y5
                "union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y10
                "union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y15
                 "union all
                (select date, close from stocks_history where symbol=? and date >= ? and date <= ? order by date asc limit 1) ". #y20
                 "union all
                (select date, close from stocks_history where symbol=? order by date asc limit 1) "; #all records
        $sql = $this->db->prepare($sql);
            $sql->bindParam(1, $code, PDO::PARAM_STR);
            $sql->bindParam(2, $d7['from'], PDO::PARAM_INT);            
            $sql->bindParam(3, $d7['to'], PDO::PARAM_STR); #7 days
            
            $sql->bindParam(4, $code, PDO::PARAM_STR);
            $sql->bindParam(5, $d10['from'], PDO::PARAM_INT);            
            $sql->bindParam(6, $d10['to'], PDO::PARAM_STR); #10 days
            
            $sql->bindParam(7, $code, PDO::PARAM_STR);
            $sql->bindParam(8, $d15['from'], PDO::PARAM_INT);            
            $sql->bindParam(9, $d15['to'], PDO::PARAM_STR); #15 days
            
            $sql->bindParam(10, $code, PDO::PARAM_STR);
            $sql->bindParam(11, $d20['from'], PDO::PARAM_INT);            
            $sql->bindParam(12, $d20['to'], PDO::PARAM_STR); #20 days
            
            $sql->bindParam(13, $code, PDO::PARAM_STR);
            $sql->bindParam(14, $day30['from'], PDO::PARAM_INT);            
            $sql->bindParam(15, $day30['to'], PDO::PARAM_STR); #30 days
            
            $sql->bindParam(16, $code, PDO::PARAM_STR);
            $sql->bindParam(17, $m3['from'], PDO::PARAM_INT);            
            $sql->bindParam(18, $m3['to'], PDO::PARAM_STR); #60 days
            
            $sql->bindParam(19, $code, PDO::PARAM_STR);
            $sql->bindParam(20, $m6['from'], PDO::PARAM_INT);            
            $sql->bindParam(21, $m6['to'], PDO::PARAM_STR); #90 days
            
            $sql->bindParam(22, $code, PDO::PARAM_STR);
            $sql->bindParam(23, $ytd['from'], PDO::PARAM_INT);            
            $sql->bindParam(24, $ytd['to'], PDO::PARAM_STR); #ytd
            
            $sql->bindParam(25, $code, PDO::PARAM_STR);
            $sql->bindParam(26, $y1['from'], PDO::PARAM_INT);            
            $sql->bindParam(27, $y1['to'], PDO::PARAM_STR); #y1
            
            $sql->bindParam(28, $code, PDO::PARAM_STR);
            $sql->bindParam(29, $y2['from'], PDO::PARAM_INT);            
            $sql->bindParam(30, $y2['to'], PDO::PARAM_STR); #y2
            
            $sql->bindParam(31, $code, PDO::PARAM_STR);
            $sql->bindParam(32, $y3['from'], PDO::PARAM_INT);            
            $sql->bindParam(33, $y3['to'], PDO::PARAM_STR); #y3
            
            $sql->bindParam(34, $code, PDO::PARAM_STR);
            $sql->bindParam(35, $y4['from'], PDO::PARAM_INT);            
            $sql->bindParam(36, $y4['to'], PDO::PARAM_STR); #y4
            
            $sql->bindParam(37, $code, PDO::PARAM_STR);
            $sql->bindParam(38, $y5['from'], PDO::PARAM_INT);            
            $sql->bindParam(39, $y5['to'], PDO::PARAM_STR); #y5
            
            $sql->bindParam(40, $code, PDO::PARAM_STR);
            $sql->bindParam(41, $y10['from'], PDO::PARAM_INT);            
            $sql->bindParam(42, $y10['to'], PDO::PARAM_STR); #y10
            
            $sql->bindParam(43, $code, PDO::PARAM_STR);
            $sql->bindParam(44, $y15['from'], PDO::PARAM_INT);            
            $sql->bindParam(45, $y15['to'], PDO::PARAM_STR); #y15
            
            $sql->bindParam(46, $code, PDO::PARAM_STR);
            $sql->bindParam(47, $y15['from'], PDO::PARAM_INT);            
            $sql->bindParam(48, $y15['to'], PDO::PARAM_STR); #y20
            
            $sql->bindParam(49, $code, PDO::PARAM_STR); #all            
            
            $sql->execute();
            $row = $sql->fetchAll(PDO::FETCH_ASSOC);
            $last = count($row)-1;
            $data_orig = array('N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A');
            $percgain = 0;
            
            foreach($row as $k=>$v){                
                $percgain = (($lprice - $v['close']) / $v['close'] ) * 100;                
                if($k == $last){                    
                    $data['all'] = moneyformatVal2($percgain).'%';                    
                }else{
                    $data[] = moneyformatVal2($percgain).'%';    
                }                
            } 
            $data = array_replace($data_orig, $data);
            
            return $data;

Wow. I’m not sure why you’d want to return something like that, but the only way I can see to really improve it would be to remove the immense number of table scans you’re doing and replacing them with MAX and MIN on the dates. Something like this

$sql = "SELECT date
	     , close
	  FROM stocks_history
	 WHERE symbol = ?
	   AND date IN (SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #7 days
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #10 days
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #15 days
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #20 days
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #30 days
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #3 months
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #6 months
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #ytd
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y1
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y2
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y3
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y4
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y5
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y10
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y15
		       " UNION ALL
			SELECT MAX(date) AS date FROM stocks_history WHERE symbol = ? AND date BETWEEN ? AND ?". #y20
		        " UNION ALL
			SELECT MIN(date) AS date FROM stocks_history WHERE symbol = ?)" . #all records			
			   " ORDER BY date DESC";

I’m sure if anyone has any better ideas on how to improve the logic flow, it would be @r937

Like I said to "get the specific data of the following dates

last 7 days, 10 days, 15 days, 30 days, 3 months, 6 months, year to
date, 1 year, 2 yrs, 3 yrs, 4 yrs, 5yrs, 10 yrs, 15 yrs, 20 yrs and the
first entry of the record."

your query doesnt get the data i need for the specific dates.
hopefully someone can give us a better solution.

Try changing the MAX to MIN on the queries. I looked at your original query wrong.

I have simplified the query to your suggestion

    SELECT date, close
FROM stocks_history
WHERE symbol = 'MEG'
       AND date IN (
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20141007 AND 20141014
               UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20141004 AND 20141014
               UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140930 AND 20141014
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140925 AND 20141014 
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140915 AND 20141014 
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140715 AND 20140815
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140415 AND 20140515
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20140101 AND 20141014
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20131015 AND 20131115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20121015 AND 20121115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20111015 AND 20111115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20101015 AND 20101115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20091015 AND 20091115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 20041015 AND 20041115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 19991015 AND 19991115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG' AND date BETWEEN 19941015 AND 19941115
            UNION ALL
            SELECT MIN(date) AS date FROM stocks_history WHERE symbol = 'MEG') 
            ORDER BY date DESC;

buts still not working. and its almost eating up all the resources.
Please help.

Create a stocks_history_excerpts table:

create table stocks_history_excerpts(
  symbol varchar(50),
  excerpt_date_offset_low int,
  excerpt_date_offset_high int,
  lowest_date date,
  close int
);

insert into stocks_history_excerpts(symbol, excerpt_date_offset_low, excerpt_date_offset_high) 
values ('MEG', 0, 7);

I only populated one row, verify and complete the rest.

After that, one single update should take care of that:

update stocks_history_excerpts a set (lowest_day, close) = (select min(date), close from stocks_history b where b.symbol = a.symbol and b.date between currdate()-a.excerpt_date_offset_low and currdate()-a.excerpt_date_offset_high)

Untested, but it should be a good starting point.

That’s not going to work because the timeliness changes every day. Seven days from Wednesday isn’t the same as seven days from Monday. Plus, that’s basically what he already has.

Do you have indexes on Symbol and on date? That would play a lot in it as well.

And that’s why I’ve put an offset, not an actual day. It should work, the update command uses CURRDATE() and those low and high offsets.

It’s completely different approach and it makes a big difference at the database level, exactly where he’s having troubles.

How frequently will the data be requested from the database (is it going to be extracted by a cron job running every so often)?

Have you tried having PHP run each query on it’s own, then appending each result set to a “master result set array”?

What indexes are in place for the stocks_history table?

Which is exactly what I’ve proposed. Every UNION ALL implies creating a new temporary table and there’s an excessive number of those, the use of IN dramatically affects performance (no use of indexes), whereas with my proposal, a stocks_history_excerpts single staging table is created instead and the update process should use more efficient database mechanisms and claim less resources from the system, while also being less verbose: one single general update vs. 15 individually tailored selects, providing the same data sets coverage. I cannot stress enough the importance of clarity the staging table brings when it comes to controlling the parameters of so many queries.

Yes it has index on symbol but on date it doesnt. the date cant be in fulltext or unique.

Can it be part of a multiple-column index that is unique?
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

it can not as everyday we have a records of the stock market
so symbols cant be make as unique only fulltext.

hi vion that’s not going to work either.
what i need is to get the data on the specific date of last 7 days, 10 days, 15 days, 20 days, 30 days, 3 months, 6 months, year to date, 1y, 2y, 3y, 4y, 5y, 10 y, 20y and the first entry of the record.

its not all data, just the specific date. everyday it changes the records as closing prices of stocks changes everyday.

Unless I’m mistaken, PHP date('Ymd') is the same with MySQL CURRDATE().

'from' => date("Ymd",strtotime("-7 days")), 'to' => date('Ymd') translates to b.date between currdate()-0 and currdate()-7.

The first record in stocks_history_excerpts has 0 for low and 7 for high. The general form in my update: b.date between currdate()-a.excerpt_date_offset_low and currdate()-a.excerpt_date_offset_high applied to the first row is exactly correct.

Hence the update with currdate(). Run it every day.

the data is requested by any user. sample a user views the website and if he search a symbol, it should be able to display the data of those specific dates. and we have more than 1K unique users per day.