Re: Request problem (with \\)
Hi,
It works, thanks you for your help :-)
ViSolve DB Team wrote:
Hi Gabriel,
Try as:
mysql select * from forum where topoc like %%;
To search for '\', specify it as ''; this is because the
backslashes are
stripped once by the parser and again when the pattern match is made,
leaving a single backslash to be matched against. (Exception: At the
end of
the pattern string, backslash can be specified as '\\'. At the end of the
string, backslash stands for itself because there is nothing following to
escape.)
Ref:
http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html
Thanks
ViSolve DB Team.
- Original Message - From: Gabriel Linder
[EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, January 19, 2007 9:43 PM
Subject: Request problem (with \\)
Hello list,
I am currently trying to fix a bug in a search function with a
request like this one :
select * from forum where topic like '%[...]%' ;
where [...] is a string escaped by mysql_real_escape_string (C API)
and topic is a varchar field (not null).
It works, but there is a bug if someone is searching the character
backslash only ('\'), so the request become :
select * from forum where topic like '%\\%' ;
and it returns only topics who have a '%' in them, not a '\'. It is
the same result as if I were doing :
select * from forum where topic like '%\%' ;
To get the topics with a '\' (but it returns only the topics that
ends with a '\'), I must do :
select * from forum where topic like '%\\' ;
So it seems to me that the ending % is escaped even with '\\'. Is
this a normal behaviour ? Or am I missing something ?
Here are some infos about the server version, might be useful :
version = 4.0.20-standard
version_comment = Official MySQL-standard binary
version_compile_os = linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query about MySQL
Hi All I got your mail id from one of the forums. can u plz tell me tht wht is the capacity of storage of MySQL DB. acutally we are in the process of making a project on LAMP technology, but the databse is too large..say 1722 records this much of records will remain in one table only... Can you please tell me . - Is mysql is able to manage this huge amount of data. - Can we put this much of data in single table database OR keeping the data in multiple tables is the good practice. - What precautions I have to taking while managing this much of data in MySQLetc etc kindly see the matter n reply me in this regards Thanx in advance raman
Re: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
The second major problem I am running into after the upgrade is the
following error, which did not occur on Development.
Error Executing Database Query. Cannot convert value '-00-00
00:00:00'
from column 4 to TIMESTAMP.
The error occurred on line 8.
MySQL version is: 5.0.27
Thanks in advance for any help.
I don't know what/how are you doing and I don't even know enough about the
subject but:
From PHP manual @ http://pt.php.net/manual/en/function.strtotime.php:
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54
GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond
to the minimum and maximum values for a 32-bit signed integer.) Additionally,
not all platforms support negative timestamps, therefore your date range
may be limited to no earlier than the Unix epoch. This means that e.g. dates
prior to Jan 1, 1970 will not work on Windows, some Linux distributions,
and a few other operating systems. PHP 5.1.0 and newer versions overcome
this limitation though.
From MySQL manual @
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html:
The range is '1970-01-01 00:00:01' UTC to partway through the year 2037.
TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01
00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00'
because that is equivalent to 0 seconds from the epoch and the value 0 is
reserved for representing '-00-00 00:00:00', the “zero” TIMESTAMP value.
HTH
- Nuno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
I'm simply executing a SELECT query that used to work before, same
database, everything the same except a higher version of the MySQL server.
-Original Message-
From: Nuno Oliveira [mailto:[EMAIL PROTECTED]
Sent: Monday, 22 January 2007 7:33 PM
To: mysql@lists.mysql.com
Subject: Re: Cannot convert value '-00-00 00:00:00' from column 4 to
TIMESTAMP
The second major problem I am running into after the upgrade is the
following error, which did not occur on Development.
Error Executing Database Query. Cannot convert value '-00-00
00:00:00'
from column 4 to TIMESTAMP.
The error occurred on line 8.
MySQL version is: 5.0.27
Thanks in advance for any help.
I don't know what/how are you doing and I don't even know enough about the
subject but:
From PHP manual @ http://pt.php.net/manual/en/function.strtotime.php:
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901
20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that
correspond to the minimum and maximum values for a 32-bit signed integer.)
Additionally, not all platforms support negative timestamps, therefore your
date range may be limited to no earlier than the Unix epoch. This means that
e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux
distributions, and a few other operating systems. PHP 5.1.0 and newer
versions overcome this limitation though.
From MySQL manual @
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html:
The range is '1970-01-01 00:00:01' UTC to partway through the year 2037.
TIMESTAMP values are stored as the number of seconds since the epoch
('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value
'1970-01-01 00:00:00'
because that is equivalent to 0 seconds from the epoch and the value 0 is
reserved for representing '-00-00 00:00:00', the zero TIMESTAMP value.
HTH
- Nuno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
I'm simply executing a SELECT query that used to work before, same database, everything the same except a higher version of the MySQL server. In that case you are probably facing some uncompatible issue between the versions you used. Maybe someone else is able to help you more that I :( Sorry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query about MySQL
Hi Raman, Is mysql is able to manage this huge amount of data. MySQL does manage this amount of data very easily. Can we put this much of data in single table database OR keeping the data in multiple tables is the good practice. You can keep this much amount of data in one single MySQL table. But incase of storing data in single large table like this, please go for a horizontal partitioning of the data as it would reduce the load very much and also the whole application will become highly scalable in future. You can actually wait for this partitioning feature till 5.1 or you can go ahead and implement your own version. (See information_schema database table in 5.1 to get a feel on how to implement this) What precautions I have to taking while managing this much of data in MySQLetc etc Actually 17 million rows is not too huge a data that make you worry about performance. But try to have a machine with 2CPU and 2G RAM with decent disk speeds. That should be enough. Make changes to your my.cnf on the similar lines given in medium.my.cnf (or the appropriate one that suits your H/W requirements) that is provided by MySQL along with all the distributions. can u plz tell me tht wht is the capacity of storage of MySQL DB. To get a very good approximate of how much space the MySQL DB is going to take, better generate dummy data and load it into the DB and use the sql statement 'show table status' to see the exact space usage. Or create the table in the MySQL DB with the same structure and load a sample of 100-200 rows and find the average row length (show table status). Multiply the average row length with the number of rows (17milliion for your case). This also gives you a good estimate. Don't forget to account for the space used by indexes here. Use MySQL 5.0 to see better space usage from MySQL side. It uses the compact row format -- compress rows before storing them. Hoe this helps. Thank you Sujay _ From: Raman Kheterpal [mailto:[EMAIL PROTECTED] Sent: Monday, January 22, 2007 2:47 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Query about MySQL Hi All I got your mail id from one of the forums. can u plz tell me tht wht is the capacity of storage of MySQL DB. acutally we are in the process of making a project on LAMP technology, but the databse is too large..say 1722 records this much of records will remain in one table only... Can you please tell me . - Is mysql is able to manage this huge amount of data. - Can we put this much of data in single table database OR keeping the data in multiple tables is the good practice. - What precautions I have to taking while managing this much of data in MySQLetc etc kindly see the matter n reply me in this regards Thanx in advance raman
Re: Query about MySQL
Hi, 1) In MySQL, the size of the table limits to the storage engine and the file system size. [minimum -default -2GB] 2) As of our understanding, keeping in multiple tables is a good practice, that too with perfect normalization. If multiple tables, table sizes are in hand, limit of file system. File volume size is a notable parameter when moving for multiple tables. 3) Regular backups. Enable log-bin; in case of recovery, use mysqlbinlog. Thanks ViSolve DB Team - Original Message - From: Raman Kheterpal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, January 22, 2007 2:46 PM Subject: Query about MySQL Hi All I got your mail id from one of the forums. can u plz tell me tht wht is the capacity of storage of MySQL DB. acutally we are in the process of making a project on LAMP technology, but the databse is too large..say 1722 records this much of records will remain in one table only... Can you please tell me . - Is mysql is able to manage this huge amount of data. - Can we put this much of data in single table database OR keeping the data in multiple tables is the good practice. - What precautions I have to taking while managing this much of data in MySQLetc etc kindly see the matter n reply me in this regards Thanx in advance raman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
low-priority-updates and innodb tables
Здравствуйте, mysql. Hi all. I want to ask about low-priority-updates and innodb tables. Does low-priority-updates=1 affect on priority of select or update query on innodb type tables? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
This is because with version 4.1 and higher the format of timestamp changes to -00-00 00:00:00. We had this problem, so we had to add a few lines in the scripts that take this field and work with it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MCN's video website is now back Alhamdulillah
MAFHH (Masoomeen) Cable Network
Website: http://www.mcn786.net
Allah only desires to keep away the uncleanness from you, O people of the
House! and to purify you a (thorough) purifying.{33:33}
Respected Brothers Sisters,
Assalam-o-Alaikum Wa Rahmatullahi Wa Barakatahu
We are pleased to inform you all that MCN website is Alhamdulillah back with
great additions and improved features. We have tried our utmost in serving
Momineens worldwide by providing best Lectures/Majalis in these sacred
months. We are thankful to Allah (SWT) for making us an intermediater for
such cause.
Here is a little introduction of MCN in short:
Seven years ago this project came into existence with the holy cause of
protecting the Islamic society from the western ethics which were being
spread by utilizing media as a source specifically. As days passed we felt
the actual importance of the media and by the grace of Almighty ALLAH today
we are able to provide 40 channels on our cable in which 20 channels are on
strict, uncompromised and well-defined censor policy which provides maximum
safety Alhmadulillah to all parents for youngsters at the time when they
have no extra security to guide kids from immoral programs. It also includes
one MCN Video Channel, which is complete Family Channel and it is designed
to broadcast LIVE recorded Majalis, Dars, Seminars, Duas on their specific
timings, Drama serials, educational programs for children which helps them
learn about ISLAM and Masoomeen(A.S). Now the covering area of this project
includes Soldier Bazar, Gurumandir, Lasbaila, Garden East, Taj Complex,
Lines Area and P.E.C.H.S and their connected areas. For more details click
here http://www.mcn786.net/about_us.html
In this holy month of Moharram Al Haram, by the Grace of Allah Almighty we
will be uploading 10 video Majalis and 2 audio ladies Majalis daily from 1st
Moharram to 10th Moharram (Ashura). Albums of famous Nauha Khuwans for the
year of 2007 will also be available on MCN's website.
Visit us on web: http://www.mcn786.net
Subscribe to MCN http://www.mcn786.net/subscribe.html 's mailing list and
you will be notified of all uploads and MCN Video Channel Schedules.
O Allah! Hasten the reappearance of the avenger of the blood of Imam Husain
(a.s.), the last hope of Janabe Zahra (s.a.) Hazrat Baqiatullah Al-Azam
(a.s.). Ameen.
_
Fazlul Abbas Panjwani
Chief Executive
MAFHH (Masoomeen) Cable Network
E-mail: [EMAIL PROTECTED]
To unsubscribe, send a blank mail to [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
Help indexing this query.
--- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
--- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Include a stored procedure ( that returns a recordset ) in another query
At 3:00 PM +1100 1/22/07, Daniel Kasak wrote: Hi all. I'd like to be able to define a stored procedure that returns a recordset ( I've done this bit ), and then select from this in another query, eg: CREATE [EMAIL PROTECTED] PROCEDURE `sp_MyClients`( IN ACCMAN INT ) BEGIN select * from mirror_Clients where AccMan = ACCMAN; END ... and then: select C.FileNo, C.SubNo, L.AccountNo from ( sp_MyClients ( 192 ) ) C inner join TLocations T on C.ClientID = T.ClientID; I've also tried adding 'call' immediately before the 'sp_MyClients' bit. Either way, I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( 192 ) ) C inner join TLocations T on C.ClientID = T.ClientID' at line 1 I assume it's not possible to do this then? I realise I can skip the stored procedure in this case and just add the where clause from the SP to the query, but in some other cases, the SP bit will be quite complex. Stored procedures that generate result sets return them directly to the client, not to another query. Perhaps you could accomplish what you want by selecting the result set into another table and reading from that table in subsequent queries. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
Hi thanks for replying. I can't immediately see the difference between your sample and mine, except the single quotes are missing, is that it? Thanks in advance. -Original Message- From: tere [mailto:[EMAIL PROTECTED] Sent: Monday, 22 January 2007 9:12 PM To: mysql@lists.mysql.com Subject: RE: Cannot convert value '-00-00 00:00:00' from column 4 to TIMESTAMP This is because with version 4.1 and higher the format of timestamp changes to -00-00 00:00:00. We had this problem, so we had to add a few lines in the scripts that take this field and work with it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
On Sunday 21 January 2007 16:41, Nuno Vaz Oliveira wrote: Just FYI: you can get the Express version of the VB.Net portion of Visual Studio for free: http://msdn.microsoft.com/vstudio/express/default.aspx That way, you're much more likely to have a fully supported app. I've not tried running VB6 apps on Vista. :) I wasn't able to find the EULA for the express products... And I didn't even know about free stuff from Microsoft. Anyway, can you tell me if the express editions are free for comertial use? I mean, this is not for a student work. It's for a company's use... While it is officially for hobbyists, students, and novice developers due to some missing features versus the entire Visual Studio Suite, according to http://msdn.microsoft.com/vstudio/express/support/faq/ : 4. Can I use Express Editions for commercial use? Yes, there are no licensing restrictions for applications built using the Express Editions. So, you're free and clear. Hope that answers your questions. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
Andrew, couple of suggestions: 1 - where you use s.status='2' change it to s.status=2 otherwise MySQL is likely casting your data from int to string, which is slow and also precludes using an index. 2 - in this case, instead of using a left join, try using a subquery: WHERE ... AND s.mid NOT IN (SELECT mid FROM ptsContestExclude) - or - change your index around, from UNIQUE (cid,mid) to UNIQUE (mid,cid) due to the way MySQL uses indices you need the queried-upon column(s) listed first(earlier) in the index. These might speed things up HTH, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
Thanks for the casting tip. Dan Buettner-2 wrote: Andrew, couple of suggestions: 1 - where you use s.status='2' change it to s.status=2 otherwise MySQL is likely casting your data from int to string, which is slow and also precludes using an index. 2 - in this case, instead of using a left join, try using a subquery: WHERE ... AND s.mid NOT IN (SELECT mid FROM ptsContestExclude) - or - change your index around, from UNIQUE (cid,mid) to UNIQUE (mid,cid) due to the way MySQL uses indices you need the queried-upon column(s) listed first(earlier) in the index. These might speed things up HTH, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8513291 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup: Copy MYSQL Directory or use MysqlDump
I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing space characters ... char(160)? ... char(194)?
Hi all.
I'm trying to weed out garbage that comes from copying and pasting stuff
from a web page.
Some of the data has spaces, but a *different* kind of space ... a
char(160) kind ... I think ... I figured this out by copying the space
character and pasting it into mysql thus:
select ascii(' ');
... where the space was pasted in.
So I'm using:
update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set Service_Number =
replace( Service_Number, char(160), '' );
... but this returns:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 313 Changed: 0 Warnings: 0
So it's not finding char(160) in Service_Number. If I try another way to
get at the space character, I get a different result:
select ascii( right( Service_Number, 1 ) ) from
tmp_AAPT_OnlineAnalyser_ChargeTypeSummary;
... gives me a big set of results, all 194 ( ie char(194) ). But when I
compare both the characters:
select char(160), char(194);
... I get:
+---+---+
| char(160) | char(194) |
+---+---+
| A0 | C2 |
+---+---+
... and both the A0 and C2 results are in reverse video. The A0
*looks* like the stuff I'm getting at the end of fields when I just do a
select from the table in the MySQL command-line client, eg the 1st
record has Service_Number:
0298437600A0
( A0 is reversed ).
Lastly, maybe I shouldn't add this, but when I construct the space
character from a Perl app running under Windows 2000:
my $space_character = chr(160);
and then insert it into the SQL:
my $sql = update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set
Service_Number = replace( Service_Number, ' . $space_character . ', '' );
it works! But the *exact* same Perl code running on a Linux client fails
( doesn't update the field anyway ). It defies logic.
Who knows what's going on?
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL reporting an error with subquery query
Hello Fellow MySqueelers!, SELECT version() reveals... 4.0.24-nt-max-log I have looked at the following query a hundred times, and cannot find how MYSQL can report a syntax error to me. query string is... SELECT RAND() AS `RAND`, `prod`.`id` FROM `prod` JOIN `pack` ON `prod`.`id` = `pack`.`pid` WHERE `pack`.`did` = 3 AND NOT ( `prod`.`id` IN ( SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `oitm1` ON `ogrp1`.`id` = `oitm1`.`gid` JOIN `pack` AS `pack1` ON `oitm1`.`pid` = `pack1`.`id` WHERE `ogrp1`.`raid` = 9 AND `ogrp1`.`wid` = 3 AND `ogrp1`.`rdate` DATE_SUB( CURDATE(), INTERVAL 3 MONTH ) ) ) ORDER BY `RAND` LIMIT 3 Error number is 1064 Error message is 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `o' any suggestions before i squeel? -wh
Re: MYSQL reporting an error with subquery query
In the last episode (Jan 23), A Blossom of Paradise said: SELECT version() reveals... 4.0.24-nt-max-log I have looked at the following query a hundred times, and cannot find how MYSQL can report a syntax error to me. 4.0 does not support subqueries. You will need to upgrade to at least 4.1, preferably 5.0. If you are stuck with 4.0, take a look at http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html for hints on rewriting the query using joins or temporary tables. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J
Re: Backup: Copy MYSQL Directory or use MysqlDump
Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tallydb.TFMM.FLD_ASSIGNED_TO rows: 1 Extra: NULL *** row 3 *** table: TFMOT type: ref possible_keys: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - From: Alex Arul To: Ratheesh K J Sent: Tuesday, January 23, 2007 11:57 AM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has
Re: Backup: Copy MYSQL Directory or use MysqlDump
Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Thanks Alex On 1/23/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
the monitor the threads-connected variable over a period of time and calcuate the value. you can even look at max used connections status variable and allot accordingly. BTW, is you application using demand based connections or connection pooling ? If it is using connection pooling then bumping thread-cache might not help. Please provide create table statement of the tables in question also. Thanks Alex On 1/23/07, Ratheesh K J [EMAIL PROTECTED] wrote: Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF( TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF( TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF( TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF( TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF( TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL( TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tallydb.TFMM.FLD_ASSIGNED_TO rows: 1 Extra: NULL *** row 3 *** table: TFMOT type: ref possible_keys:
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Here is the CREATE TABLE Statement for the table on which the index_merge is being applied. There will be atleast 10 queries always running on this table with an index_merge optimization. We have the max_connections variable set to 100. Also repeatedly checking the Threads_Connected status variable shows varying b/w 16 to 40.. So I guess increasing the thread_cahce_size from 8 to 40 will help... Or shld it be even less? CREATE TABLE CREATE TABLE `TBL_FORUMS_MSG_MAIN` ( `FLD_MSG_ID` int(10) unsigned NOT NULL default '0', `FLD_ACC_ID` smallint(5) unsigned NOT NULL default '0', `FLD_DOMAIN_ID` tinyint(4) NOT NULL default '0', `FLD_TICKET_NUM` varchar(32) NOT NULL default '', `FLD_ADD_BY` int(10) unsigned NOT NULL default '0', `FLD_ADD_DATE_TIME` datetime NOT NULL default '-00-00 00:00:00', `FLD_UPDATE_BY` int(10) unsigned default NULL, `FLD_UPDATE_DATE_TIME` datetime default NULL, `FLD_MSG_DATE_TIME` datetime default '-00-00 00:00:00', `FLD_THREAD_ID` int(10) unsigned NOT NULL default '0', `FLD_PARENT_ID` int(10) unsigned NOT NULL default '0', `FLD_TREE_POS` int(10) unsigned NOT NULL default '0', `FLD_TREE_LEVEL` int(10) unsigned NOT NULL default '0', `FLD_RESTORE_THREAD_ID` int(10) unsigned default '0', `FLD_WORKFLOW_TYPE` tinyint(1) unsigned NOT NULL default '0', `FLD_MEDIUM` tinyint(1) unsigned NOT NULL default '0', `FLD_DIRECTION` tinyint(3) unsigned NOT NULL default '0', `FLD_ISSUE_TYPE` tinyint(3) unsigned NOT NULL default '0', `FLD_ZONE` int(11) unsigned default '0', `FLD_COMPANY_ID` int(11) unsigned default '0', `FLD_PRODUCT_ID` tinyint(4) unsigned default '0', `FLD_ASSIGNED_TO` int(10) unsigned default '0', `FLD_MSG_TYPE` tinyint(3) unsigned default '0', `FLD_MSG_INFO_ONLY_STATE` tinyint(1) unsigned default '0', `FLD_ATTACHMENT_FLAG` tinyint(1) unsigned default '0', `FLD_COUNTRY_TYPE` tinyint(1) unsigned default '0', `FLD_NO_SUPPORT_FLAG` tinyint(1) unsigned default '0', `FLD_CONTACT_PID` int(9) unsigned zerofill default NULL, `FLD_SUB_CONTACT_ID` int(10) unsigned default NULL, `FLD_BLOCK_STATE` tinyint(1) unsigned default '0', `FLD_MARK_AS_DELETED` tinyint(1) unsigned default '0', `FLD_SEEN_FLAG` tinyint(1) unsigned default '0', `FLD_REPLY_FLAG` tinyint(1) unsigned default '0', `FLD_FWD_FLAG` tinyint(1) unsigned default '0', `FLD_USR_SEEN_FLAG` tinyint(1) unsigned default '0', `FLD_USR_REPLY_FLAG` tinyint(1) unsigned default '0', `FLD_USR_FWD_FLAG` tinyint(1) unsigned default '0', `FLD_SUBM_OP_ID` tinyint(1) unsigned default '0', `FLD_ISSUE_CLOSED` tinyint(1) unsigned NOT NULL default '0', `FLD_TASK_STATE` tinyint(1) unsigned default '0', `FLD_ESCALATED_FLAG` tinyint(1) unsigned default '0', `FLD_BOUNCED_MAIL_FLAG` tinyint(1) unsigned default '0', `FLD_LEAD_ID` int(11) unsigned default '0', `FLD_BUG_ID` int(11) unsigned default '0', `FLD_EMAIL_IP_ADDRESS` varchar(15) default NULL, `FLD_EMAIL_FROM` varchar(150) default NULL, `FLD_EMAIL_TO` text, `FLD_EMAIL_CC_TO` text, `FLD_EMAIL_BCC_TO` text, `FLD_SUBJECT` varchar(100) default NULL, `FLD_PRIORITY` tinyint(1) unsigned default NULL, `FLD_TELCALL_FROMTO_NAME` varchar(128) default NULL, `FLD_TELCALL_FROMTO_DESC` varchar(64) default NULL, `FLD_CHAT_FROM_NAME` varchar(32) default NULL, `FLD_CHAT_FROM_DESC` varchar(64) default NULL, `FLD_CHAT_START_DATE_TIME` datetime default '-00-00 00:00:00', `FLD_CHAT_END_DATE_TIME` datetime default '-00-00 00:00:00', `FLD_CHAT_SESSION_ID` int(11) default '0', `FLD_CSS_INTERACTION_TYPE` tinyint(1) unsigned default '0', `FLD_CSS_ISSUE_CATEGORY` tinyint(1) unsigned default '0', `FLD_CSS_ISSUE_TAT_QTY` tinyint(1) unsigned default '0', `FLD_CSS_ISSUE_TAT_UNIT` varchar(6) default NULL, `FLD_CSS_ISSUE_SLA_QTY` tinyint(1) unsigned default '0', `FLD_CSS_ISSUE_SLA_UNIT` varchar(6) default NULL, PRIMARY KEY (`FLD_MSG_ID`), KEY `TREE_POS` (`FLD_TREE_POS`), KEY `FLD_MEDIUM` (`FLD_MEDIUM`), KEY `FLD_MSG_TYPE` (`FLD_MSG_TYPE`), KEY `FLD_PARENT_ID` (`FLD_PARENT_ID`), KEY `FLD_ADD_DATE_TIME` (`FLD_ADD_DATE_TIME`), KEY `FLD_CONTACT_PID` (`FLD_CONTACT_PID`), KEY `FLD_ASSIGNED_TO` (`FLD_ASSIGNED_TO`), KEY `FLD_THREAD_ID` (`FLD_THREAD_ID`), KEY `FLD_EMAIL_FROM` (`FLD_EMAIL_FROM`), KEY `FLD_TICKET_NUM` (`FLD_TICKET_NUM`), KEY `FLD_MARK_AS_DELETED` (`FLD_MARK_AS_DELETED`), KEY `FLD_ACC_ID` (`FLD_ACC_ID`), KEY `FLD_BLOCK_STATE` (`FLD_BLOCK_STATE`), KEY `FLD_FWD_FLAG` (`FLD_FWD_FLAG`), KEY `FLD_ISSUE_CLOSED` (`FLD_ISSUE_CLOSED`), KEY `FLD_REPLY_FLAG` (`FLD_REPLY_FLAG`), KEY `FLD_SUBJECT` (`FLD_SUBJECT`), KEY `FLD_WORKFLOW_TYPE` (`FLD_WORKFLOW_TYPE`), KEY `FLD_PRODUCT_ID` (`FLD_PRODUCT_ID`), KEY `FLD_SUB_CONTACT_ID` (`FLD_SUB_CONTACT_ID`), KEY `FLD_ESCALATED_FLAG` (`FLD_ESCALATED_FLAG`), KEY `FLD_BOUNCED_MAIL_FLAG` (`FLD_BOUNCED_MAIL_FLAG`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 - Original Message - From: Alex Arul To: Ratheesh K
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hi, Here, threads_connected is considerable and below the preset value. The threads_connected and threads_running are the good indicators to see how loaded the server is. In your case it is good numbers. So use 'iostat'/relavant utility to monitor the DB activity. Also threads_created is more, which should be low. so to average it increase the thread_cache size to some 64 or more. Ref: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html Thanks ViSolve DB Team - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 11:59 AM Subject: Urgent - MySQL 5 - mysqld using a lot of memory consistently Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
