gmane.comp.db.mysql.general

Query weirdness...

Don Wieland — 2012-05-25T19:58:42

I am hoping someone can bail me out on why this query in not working.

In the queries below:

query 1 returns multiple rows - one per client.

in the rows where:

client_id is 254240, dr_all_ther_qty = 1
client_id is 253821, dr_all_ther_qty = 1
client_id is 254023, dr_all_ther_qty = 6

But when I break out the queries into their own queries (queries  
2,3,4), I get different result (WHICH are ACCURATE - the ones I need):

query 2 - dr_all_ther_qty = 0
query 3 - dr_all_ther_qty = 0
query 4 - dr_all_ther_qty = 5

Clueless on why these DO NOT reconcile. I really need query 1 results  
to be the same results as queries 2,3, and 4. Little help!

--- start query 1 ---

SELECT q1.* FROM

(SELECT
apt.user_id,
apt.client_id,
c.last_name,
c.first_name,
MIN(apt.time_start) AS stime,
FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m-%d') AS ftime,
(SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id =  
tl_appt.room_id
WHERE client_id = apt.client_id AND r.location_id = '1' AND  
appt_status_id = '3' AND time_

Re: category with parentid

Peter Brawley — 2012-05-25T16:31:29

It's a graph! See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

PB

-----

Re: category with parentid

Andrés Tello — 2012-05-25T15:07:25

You have a typical hierarchical data issue, can be solved with a graph.
Theory review: http://www.algorithmist.com/index.php/Graph_data_structures

Mysql and PHP implementation:
http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html

Re: category with parentid

2012-05-25T13:35:39

id | name   | parentid
----------------------
1 | cat A      | 0
2 | cat B      | 0
3 | subcat A | 1
4 | subcat A | 1
5 | subncat B | 2
-------------------------

I want to display the result like this:

1. Cat A
      - Subcat A
      - Subcat A
2. CatB
      - Subcat B
<<<<<<<<
If the degree of subordination is a number in your record, try function REPEAT (output concatenated to A or B), or LPAD.

**************************************************************
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**************************************************************

Re: ANN: Hopper for MySQL, first public beta available!

Claudio Nanni — 2012-05-25T11:57:02

Welcome to Hopper!

Claudio

2012/5/25 Martijn Tonies <m.tonies< at >upscene.com>

ANN: Hopper for MySQL, first public beta available!

Martijn Tonies — 2012-05-25T10:44:54

Dear ladies and gentlemen,

Upscene Productions is happy to announce the launch of a new
database-developer tool:
"Hopper" (public beta 1)

Hopper is a Stored Routine Debugger, the first beta for MySQL
is now available.


For more information, see 
http://www.upscene.com/displaynews.php?item=20120525


With regards,

Martijn Tonies

Upscene Productions
http://www.upscene.com

category with parentid

HaidarPesebe — 2012-05-25T07:57:46

I have a database for the category

id | name   | parentid
----------------------
1 | cat A      | 0
2 | cat B      | 0
3 | subcat A | 1
4 | subcat A | 1
5 | subncat B | 2
-------------------------

I want to display the result like this:

1. Cat A
      - Subcat A
      - Subcat A
2. CatB
      - Subcat B

ask for help for this.
Thanks for the help



__________ Information from ESET NOD32 Antivirus, version of virus signature database 7165 (20120524) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

RE: Architectural Help

Rick James — 2012-05-24T23:41:42

I deal with dozens of systems, all doing cross-country or cross-ocean replication.  The only viable approach (that we have deployed in production) is
* Dual-master, single writer -- That is, all 49 clients write to one machine
* The two masters are geographically separate.
* Failover involves switching to the 'other' master.  It is _not_ fully automated.
* Slaves, as needed, scattered around the world -- This provides read scaling.

1M writes per day -- that is an average of 12/sec.  Yawn.
Replication delay -- you probably will almost never see any.
Network outages -- one of many things that can cause trouble.  The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc.

RE: Need help for performance tuning with Mysql

Rick James — 2012-05-24T23:32:26

Thanks.  I got tired of answering the same questions about buffer_pool and key_buffer over and over on forums.mysql.com; now I just point people at that page.

INT(1) and INT(50) are identical -- and take 4 bytes.  See TINYINT, BIGINT, etc.  Also, UNSIGNED is probably wanted in more places than you have it.

555KB is not very big.  But a table scan (as indicated by the EXPLAIN) costs something.

select  *             -- Is this what you are really fetching?  If not, we can discuss a "covering" index.
    from  thold_data
    where  thold_enabled='on' -- 2-valued flag?  Not likely to be useful in an index, esp. not by itself
      AND  data_id = 91633;   -- Probably the best bet.

Recommend:
INDEX(data_id)  -- or UNIQUE, if it is unique
INDEX(data_id, thold_enabled)  -- or the opposite order; this probably would not be noticeable better.

`notify_default` enum('on','off') default NULL
Did you really mean to have 3 values (on, off, NULL)?

RE: large temp files created by mysql

Luis Daniel Lucio Quiroz — 2012-05-24T16:21:08

For my propouses aprox is fine. I guess for others it doesnt
El may 24, 2012 9:59 a.m., "Rick James" <rjames< at >yahoo-inc.com> escribió:

RE: large temp files created by mysql

Rick James — 2012-05-24T14:58:47

Maybe.

1. The "Rows" is approximate, so you could over-shoot or under-shoot the end of the table.

2. OFFSET (limit $r,1) still has to scan $r rows.

3. SELECT * with an OFFSET will read the entire rows.  SELECT `foo`, where `foo` is indexed, but not the InnoDB PRIMARY KEY, will scan only the INDEX(foo).  This is likely to be much faster.  But you are unlikely to do that unless foo is UNIQUE.

A slight improvement (addressing both issues) is to decide which end to approach from.  But scanning from the end needs an ORDER BY, preferably on the PRIMARY KEY.

etc.

Re: Architectural Help

Anupam Karmarkar — 2012-05-24T10:39:25

Dear Nigel,

Thank for reply.. 


See my comments below


--Anupam


________________________________
 From: Nigel Wood <nwood< at >plus.net>
To: Anupam Karmarkar <sb_akarmarkar< at >yahoo.com>; "mysql< at >lists.mysql.com" <mysql< at >lists.mysql.com> 
Sent: Thursday, 24 May 2012 3:35 PM
Subject: RE: Architectural Help
 
A few questions:
which is more or a problem: network outages, network capacity or query latency? 
When you say "near real-time" do you need transactional consistent view on all 49 servers or can some lag be tolerated?
Can any one of the 49 local servers potentially update/delete the same rows or data?
Is there any natural segmentation point within the data? 
Do the data centers have diverse networks so that connections to some data centers may remain when others? 
In the event that a local data centre is totally isolated from the others what data should it be allowed to update?
Do your applications produce/examine  large data set querying by secondary keys or using tull text search?
Are you in a position to m

RE: Architectural Help

Nigel Wood — 2012-05-24T10:05:30

A few questions:
which is more or a problem: network outages, network capacity or query latency? 
When you say "near real-time" do you need transactional consistent view on all 49 servers or can some lag be tolerated?
Can any one of the 49 local servers potentially update/delete the same rows or data?
Is there any natural segmentation point within the data? 
Do the data centers have diverse networks so that connections to some data centers may remain when others? 
In the event that a local data centre is totally isolated from the others what data should it be allowed to update?
Do your applications produce/examine  large data set querying by secondary keys or using tull text search?
Are you in a position to modify the applications?
__________________________________
From: Anupam Karmarkar [sb_akarmarkar< at >yahoo.com]
Sent: Thursday, May 24, 2012 10:17 AM
To: mysql< at >lists.mysql.com
Subject: Architectural Help

Hi All,


I need architectural help for our requirement,


We have nearly 50 data centre through out dif

Re: Architectural Help

Ananda Kumar — 2012-05-24T09:35:10

is the central database server just ONE server, to which all your 50 data
center app connects

On Thu, May 24, 2012 at 2:47 PM, Anupam Karmarkar
<sb_akarmarkar< at >yahoo.com>wrote:

Architectural Help

Anupam Karmarkar — 2012-05-24T09:17:12

Hi All,


I need architectural help for our requirement, 


We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server.  Application consists of write/read/delete operations, 


Current writes each day central server 1million. 


Only 1/1000 need to be distrubuted acrross servce rest need to be in central server.


How can we achive this ? solution needs very much real time data accepting nework lags. 


Solution

Collect all changes in other 49 server into 1 central server(How can we collect data)


49 keeps updating data into local database from central server(Using Repliation Can be done) 



--Anupam

Re: Need help for performance tuning with Mysql

Yu Watanabe — 2012-05-24T08:22:28

Johnny

Thank you for the reply.


  The problem is mainly on MyISAM engine.


  The server has no RAID.

Thanks,
Yu

Johnny Withers さんは書きました:

Re: Need help for performance tuning with Mysql

Yu Watanabe — 2012-05-24T07:50:05

Alex

Thank you for the advice.

Probably, we will put index (key) on both columns.

Thanks,
Yu

Alex Schaft さんは書きました:

Re: large temp files created by mysql

Luis Daniel Lucio Quiroz — 2012-05-24T06:59:43

I got a solution maybe

step 1:
mysql> explain select * from users;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows     | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    |
NULL | 32883093 |       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)

so you get the "rows" field

Step2:
select * from users, limit $r,1


What do you think? Is the only way i found what delays seconds not
minuts. USERS is a 19GB Table for me.

LD

2011/10/30 Jan Steinman <Jan< at >bytesmiths.com>:

Re: Need help for performance tuning with Mysql

Alex Schaft — 2012-05-24T05:55:49

On second thought, an index on thold_enabled won't mean much I think, so
either leave it off or create an index on data_id plus thold_enabled.
Someone more knowledgeable may correct me.

Alex

Re: Need help for performance tuning with Mysql

Alex Schaft — 2012-05-24T05:37:11

You are selecting a record based on the value of data_id and
thold_enabled, but don't have an index on either? Add an index for both.
If data_id is unique, then you would only need an index on that.

Alex

Re: Need help for performance tuning with Mysql

Yu Watanabe — 2012-05-24T02:10:07

Rick

Thank you for the reply.


  The page is really cool. Its very simple and easy to understand.


  | thold_data | CREATE TABLE `thold_data` (
    `id` int(11) NOT NULL auto_increment,
    `rra_id` int(11) NOT NULL default '0',
    `data_id` int(11) NOT NULL default '0',
    `thold_hi` varchar(100) default NULL,
    `thold_low` varchar(100) default NULL,
    `thold_fail_trigger` int(10) unsigned default NULL,
    `thold_fail_count` int(11) NOT NULL default '0',
    `thold_alert` int(1) NOT NULL default '0',
    `thold_enabled` enum('on','off') NOT NULL default 'on',
    `bl_enabled` enum('on','off') NOT NULL default 'off',
    `bl_ref_time` int(50) unsigned default NULL,
    `bl_ref_time_range` int(10) unsigned default NULL,
    `bl_pct_down` int(10) unsigned default NULL,
    `bl_pct_up` int(10) unsigned default NULL,
    `bl_fail_trigger` int(10) unsigned default NULL,
    `bl_fail_count` int(11) unsigned default NULL,
    `bl_alert` int(2) NOT NULL default '0',
    `lastread` varchar(100) default NULL