MySQL & High Server Load

T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Q2:

explain extended SELECT x.id, x.betgame_id, x.coupon_code, x.home_team, x.visit_team, x.code, x.start_timestamp, d.start_date, d.start_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-11-27' AND (d.status IS NOT NULL OR d.status_game IS NOT NULL OR d.start_date IS NOT NULL) ORDER BY d.id ASC

Q3:

explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.*
from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id
where d.`full_id`=589
and (k.`status`='active' || k.`status`='blocked')
order by d.`rec_time` desc, d.`id` desc

Q4:

explain extended SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-11-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL GROUP BY d.bet_field, d.betgame_id , d.coupon_code , d.code ORDER BY id DESC



See the thing is that your slow query log is geenrated from
2013-09-25 09:15:52 to 2013-11-28 13:22:57

when you posted your topic 11-25-2013, 04:53 AM

so it would be good to generate slow log only from last 24h
so remove current slow log /var/log/mysql/mysql-slow.log
and run
mysql
flush logs;

to recreate it again for 24 h
 
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Ok I got it.

Q2 + Q4:
Q3 says: "ERROR 1146 (42S02): Table 'masterbe_app.kouponi_xml_diff' doesn't exist".
I flushed slow.log as well.

Q3 was in another database sorry..
Code: 
mysql> explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+
| id |select_type |table |type |possible_keys |key |key_len |ref  |rows |filtered |Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+
|1|SIMPLE|k |ALL| NULL |NULL |NULL |NULL |13286 |100.00 |Using where; Using temporary; Using filesort |
|1 |SIMPLE|d |ALL |NULL| NULL |NULL |NULL |866238 |100.00 |Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+
2 rows in set, 1 warning (0.00 sec)
 
Last edited:
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
ok, please also run to show current index scheme

show indexes from kouponidb_kino_wins_xls;

show indexes from kouponidb_prognosi_xml;
show indexes from kouponidb_prognosi_xml_diff;

show indexes from kouponi_xml;
show indexes from kouponi_xml_diff;

show indexes from kouponidb_kino_delaysrepeats;

please also describe which of those tables are MyiSAM or which InnoDB

edit:
Q3 is not using indexes at all, its very expensive, based on stats 95% of those queries are executing 57s

here is simple way to improve it
kouponi_xml_diff, add index, name betgame_id_full_id, and 2 fields there: betgame_id, full_id
kouponi_xml, add index, name betgame_id_status, and 2 fields: betgame_id, status

also add individual indexes on
kouponi_xml_diff id field and rec_time field, so mysql optimizer can choose which one is better

after than please rerun

explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc;

it would be good to do those changes after you generate 24h slow log, so we could compare the results based on slow log,

if you dont want to wait

please run
select SQL_NO_CACHE k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc;

few times, during normal load from phpmyadmin, save its execution time on paper
then add indexes and run the query again, saving execution times for comparision
 
Last edited:
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
I am going to wait 24 hours as you suggested before adding those indexes.
Is there anything else I can do until then in order to decrease cpu consumption by mysql?
Did you get any useful info from Indexees.doc?
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Based on index structure of the tables I can say that the person who made it, doesn't understand indexes at all :)

When you have new slowlog ready we will discuss the rest
 
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Hi!Could you please assist me with the following problem?
I have this query:

Code: 
SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, d.rec_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL AND (x.id, d.rec_time, d.bet_field) in (select x.id, max(d.rec_time) rec_time, d.bet_field FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL group by x.id, d.bet_field order by bet_field) GROUP BY d.bet_field, d.betgame_id , d.coupon_code , d.code ORDER BY id DESC;
Although I have index full_id_bet_game with fields full_id, betgame_id, coupon_code and code, the explain extended output shows that kouponidb_prognosi_xml_diff table has no indexees.

Code: 
+----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+
|id |select_type |table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+
| 1 | PRIMARY | d | ALL  | NULL | NULL | NULL    | NULL | 570965 |   100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | x | ref  | Index_2,betgame_id_status | betgame_id_status | 4 | masterbe_app.d.betgame_id | 1 |  100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | d  | ALL  | NULL | NULL | NULL | NULL | 570965 | 100.00 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | x  | ref  | Index_2,betgame_id_status | betgame_id_status | 4 | masterbe_app.d.betgame_id | 1 |   100.00 | Using where                                  |
+----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+
4 rows in set, 1 warning (0.00 sec)
 
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Ok i removed full_id from the indexes and now i have Index_2,betgame_id_status as possible_keys but key field still remains null in explain extended output.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Sorry for late reply, but its very time consuming to help you witht this via forums, it would be much faster to get access to it and work directly
I don't really have time to explain it all here, so I will refer to last post

If you are a programmer of this, you don't much understand the idea of indexes and how they work in MySQL

From the show indexes command you posted on table kouponidb_prognosi_xml_diff, you got there 2 indexes
1 - PRIMARY of id column
2 - full_id of 13 columns

Please remove this 2 index, couse its useless like that

kouponidb_prognosi_xml
1 - PRIMARY of id column
2 - Index_2 of coupon_code column
3 - full_id of 10 columns

same, delete 3 index, its useless when its designed out of 10 columns

but I see from explain that you got tehre in kouponidb_prognosi_xml also index betgame_id_status which Ive suggested before
betgame_id_status from 2 fields: betgame_id, status, good

since status is not used in WHERE NOR JOIN, its only using betgame_id from this index

so you can have better index there for this particular query

ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code
for both tables you can create an index based of 3 elements:
coupon_code, betgame_id, code

so add there indexes to both tables: coupon_betgame_code that consists of 3 coupon_code, betgame_id, code in that order

in kouponidb_prognosi_xml, you can even use 4 columns in this index, as last one add start_date to make use of
WHERE x.start_date >= '2013-12-23'
so it would be index
coupon_betgame_code_startdate of 4 columns coupon_code, betgame_id, code, start_date in that order

btw. if I understand the query logic correctly it can be written like this: without subqueries
you should review it, since your subquery doesnt much make sense
Code: 
SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, max(d.rec_time) rec_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL  GROUP BY d.bet_field, d.betgame_id , d.coupon_code , d.code ORDER BY id DESC;
you can run both and compare results

and please also run explain extended on both and show us the result

and before you should run your old query few times to measure execution time

just add SQL_NO_CACHE after select like this
SELECT x.id, to SELECT SQL_NO_CACHE x.id,
so mysql wouldnt cache results during execution for speed check

and my suggested query,

then after adding indexes, run it again, to measure execution time
just remember to put there SQL_NO_CACHE during tests
 
Last edited:
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Hi, thanks a lot for you help once again!I really appreciate it.
The code isn't mine that's why I am struggling to find out what is going on..
I have changed indexes again as you suggested, except PRIMARY of id column because it is auto increment and phpmyadmin won't let me, but explain extended output for my query remains the same as you can see.
View image: explain extended
What else should I do?

I know that my query is very time consuming but I can't help it.Your query doesn't do the job I want.
What I want is for every bet_field type to get the latest ( max(rec_time) ) registered information for every other field.
For example:

id home_team visit_team code start_timestamp rec_time bet_field from_value to_value status status_game
14698 chelsea liverpool 227 1388320200 1388314382 b1 2 2.10 NULL NULL
14698 chelsea liverpool 227 1388320200 1388314400 b1 2.10 2.30 NULL NULL
14698 chelsea liverpool 227 1388320200 1388314400 b2 3.20 3.15 NULL NULL

I want to get the second row from the above result because it is the most resent concerning bet_field "b1" and the third one because it is the only record for bet_field "b2".
 
Last edited:
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Which indexes should I have for the following queries?

Code: 
SELECT COUNT(*) AS expression
FROM 
(SELECT kouponidb_kino_wins_xls.drawNo AS drawno, kouponidb_kino_wins_xls.timestamp AS kouponidb_kino_wins_xls_timestamp, kouponidb_kino_wins_xls.idCategory AS kouponidb_kino_wins_xls_idcategory, kouponidb_kino_wins_xls.idSelectedCategory AS kouponidb_kino_wins_xls_idselectedcategory, SUM(kouponidb_kino_wins_xls.winners) AS kouponidb_kino_wins_xls_winners, SUM(kouponidb_kino_wins_xls.winningAmount) AS kouponidb_kino_wins_xls_winningamount, 1 AS expression
FROM 
kouponidb_kino_wins_xls kouponidb_kino_wins_xls
WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') ))
GROUP BY drawno) subquery

Code: 
SELECT DISTINCT kouponidb_kino_wins_xls.drawNo AS drawno, kouponidb_kino_wins_xls.timestamp AS kouponidb_kino_wins_xls_timestamp, kouponidb_kino_wins_xls.idCategory AS kouponidb_kino_wins_xls_idcategory, kouponidb_kino_wins_xls.idSelectedCategory AS kouponidb_kino_wins_xls_idselectedcategory, SUM(kouponidb_kino_wins_xls.winners) AS kouponidb_kino_wins_xls_winners, SUM(kouponidb_kino_wins_xls.winningAmount) AS kouponidb_kino_wins_xls_winningamount
FROM 
kouponidb_kino_wins_xls kouponidb_kino_wins_xls
WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T20:42') ))
GROUP BY drawno
ORDER BY kouponidb_kino_wins_xls_winningamount DESC

Code: 
SELECT kouponidb_kino_delaysrepeats.drawNo AS drawno, kouponidb_kino_delaysrepeats.referred_drawNo AS kouponidb_kino_delaysrepeats_referred_drawno, kouponidb_kino_delaysrepeats.number AS kouponidb_kino_delaysrepeats_number, kouponidb_kino_delaysrepeats.timestamp AS kouponidb_kino_delaysrepeats_timestamp, kouponidb_kino_delaysrepeats.type AS kouponidb_kino_delaysrepeats_type, kouponidb_kino_delaysrepeats.times AS kouponidb_kino_delaysrepeats_times_1, MAX(kouponidb_kino_delaysrepeats.times) AS kouponidb_kino_delaysrepeats_times
FROM 
kouponidb_kino_delaysrepeats kouponidb_kino_delaysrepeats
WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_delaysrepeats.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-06T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_delaysrepeats.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-06T22:00') AND (kouponidb_kino_delaysrepeats.times > '0') ))
GROUP BY drawno, kouponidb_kino_delaysrepeats_referred_drawno, kouponidb_kino_delaysrepeats_number, kouponidb_kino_delaysrepeats_timestamp, kouponidb_kino_delaysrepeats_type, kouponidb_kino_delaysrepeats_times_1
ORDER BY kouponidb_kino_delaysrepeats_times_1 DESC
LIMIT 300 OFFSET 0
 
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
When my server load reaches 30-40 out of 12 processors, top command looks like this:

Code: 
top - 16:40:29 up 105 days, 20:20,  2 users,  load average: 15.52, 10.86, 11.32
Tasks: 607 total, 132 running, 475 sleeping,   0 stopped,   0 zombie
Cpu(s): 89.0%us, 10.7%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  65256396k total, 51683136k used, 13573260k free,   719384k buffers
Swap: 33554296k total,        0k used, 33554296k free, 44309028k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                  
16897 mysql     20   0 12.6g 1.1g 6760 S 19.0  1.7   9:32.60 mysqld                                                                                                                                                    
12453 root      20   0  148m  12m  896 R  8.8  0.0   0:00.47 lfd - (child) p                                                                                                                                           
12544 masterbe  20   0  210m  40m 6472 R  8.8  0.1   0:00.34 php                                                                                                                                                       
12552 masterbe  20   0  210m  40m 6472 R  8.8  0.1   0:00.34 php                                                                                                                                                       
12561 masterbe  20   0  210m  40m 6472 R  8.8  0.1   0:00.33 php                                                                                                                                                       
12562 masterbe  20   0  210m  40m 6472 R  8.8  0.1   0:00.33 php                                                                                                                                                       
12571 masterbe  20   0  210m  40m 6472 R  8.8  0.1   0:00.33 php                                                                                                                                                       
12541 masterbe  20   0  210m  40m 6472 R  8.2  0.1   0:00.32 php                                                                                                                                                       
12653 masterbe  20   0  204m  35m 6440 R  8.2  0.1   0:00.25 php                                                                                                                                                       
12656 masterbe  20   0  194m  29m 6340 R  7.9  0.0   0:00.24 php                                                                                                                                                       
12662 masterbe  20   0  172m  10m 6392 R  7.9  0.0   0:00.24 php                                                                                                                                                       
12663 masterbe  20   0  191m  22m 6464 R  7.9  0.0   0:00.24 php                                                                                                                                                       
12658 masterbe  20   0  202m  33m 6500 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12659 masterbe  20   0  201m  32m 6508 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12665 masterbe  20   0     0    0    0 R  7.5  0.0   0:00.23 php                                                                                                                                                       
12666 masterbe  20   0  192m  30m 6356 R  7.5  0.0   0:00.23 php                                                                                                                                                       
12668 masterbe  20   0  171m  10m 6416 R  7.5  0.0   0:00.23 php                                                                                                                                                       
12675 masterbe  20   0  201m  32m 6464 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12676 masterbe  20   0  192m  23m 6484 R  7.5  0.0   0:00.23 php                                                                                                                                                       
12677 masterbe  20   0  201m  32m 6504 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12678 masterbe  20   0  204m  35m 6456 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12680 masterbe  20   0  201m  32m 6508 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12714 masterbe  20   0  201m  32m 6476 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12755 masterbe  20   0  201m  32m 6468 R  7.5  0.1   0:00.23 php                                                                                                                                                       
12657 masterbe  20   0  201m  32m 6444 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12661 masterbe  20   0  199m  29m 6448 R  7.2  0.0   0:00.22 php                                                                                                                                                       
12667 masterbe  20   0  202m  32m 6532 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12669 masterbe  20   0  201m  32m 6440 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12671 masterbe  20   0  204m  35m 6456 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12672 masterbe  20   0  201m  32m 6436 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12673 masterbe  20   0  201m  32m 6464 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12682 masterbe  20   0  200m  31m 6484 R  7.2  0.0   0:00.22 php                                                                                                                                                       
12686 masterbe  20   0  201m  32m 6436 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12687 masterbe  20   0  201m  32m 6436 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12689 masterbe  20   0  201m  32m 6420 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12697 masterbe  20   0  201m  32m 6420 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12733 masterbe  20   0  201m  32m 6420 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12735 masterbe  20   0  200m  31m 6340 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12739 masterbe  20   0  201m  32m 6464 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12740 masterbe  20   0  200m  31m 6392 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12742 masterbe  20   0  201m  32m 6360 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12745 masterbe  20   0  201m  32m 6420 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12750 masterbe  20   0  201m  32m 6420 S  7.2  0.1   0:00.22 php                                                                                                                                                       
12752 masterbe  20   0  200m  31m 6340 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12757 masterbe  20   0  201m  32m 6492 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12760 masterbe  20   0  201m  32m 6488 R  7.2  0.1   0:00.22 php                                                                                                                                                       
12763 masterbe  20   0  201m  32m 6420 S  7.2  0.1   0:00.22 php
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Sorry but I can't help you thru forums like that, it would take too much time
Your case requires access to the code/server for the more detailed review

About queries
DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') ))

you need to do some study on mysql indexes etc. those are simple mistakes, this part above should be rewritten so that on the left is the column, and on the right all data modifications, so that query can utilize index
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
just modify the query so that

WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') ))
GROUP BY drawno) subquery

columns are on the left without any operations on them:

WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND kouponidb_kino_wins_xls.timestamp >= /* here comes the operations on the date */ '2014-01-03T09:00'

and then add index on timestamp
 
S

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
Sorry but I quite don't get it.
You mean something like this:

WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND kouponidb_kino_wins_xls.timestamp >=(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00'

'2014-01-03T09:00': this isn't a static value.
 
Last edited:
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
No, I mean that on the left

WHERE (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00'

There should NOT be any operations on the column, instead operations should be on the value on the right

WHERE kouponidb_kino_wins_xls.timestamp < (ADDTIME(FROM_UNIXTIME('2014-01-03T09:00'), SEC_TO_TIME(7200)))

or in the dynamic code directly

WHERE kouponidb_kino_wins_xls.timestamp < 2014-01-03T11:00'
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
Samet Chan MySQL Database crashed/CPU higher on server to down Workarounds and Optimization 10
S High MySQL and server load. Optimization Request Workarounds and Optimization 5
F Server Load High and Slowing my Website Down as Well as Killing my mySQL Workarounds and Optimization 2
J High Server Load - mysqld Workarounds and Optimization 6
S my server get high load from mysql server Workarounds and Optimization 7
Similar threads
MySQL Database crashed/CPU higher on server to down
High MySQL and server load. Optimization Request
Server Load High and Slowing my Website Down as Well as Killing my mySQL
High Server Load - mysqld
my server get high load from mysql server
Top Bottom