Re: Vista crashes

[Reindl Harald]

so why do you search a list of known bugs instead update your mysql?
5.5.8 is the FIRST ga version of 5.5
currently we have 5.5.16

no, i do not know if 5.5.16 is solving your problem but hwat i know
is that the updates were bot relöeased just for fun

Am 12.10.2011 06:28, schrieb Hal?sz S?ndor:
> I find that, when under Vista the MySQL daemon has been shut down, by giving 
> the command
> start mysqld -b"%CD%"
> in the root directory where MySQL 5.5.8 (the version running on this 
> computer) has been stored from an instance of command prompt with 
> administrator authority issued by a user that lacks it (like an ordinary user 
> s doing bare "su" in Unix) I make Vista crash with the blue-screen message 
> "process or task critical to system operation has been terminated or exited".
> 
> The "start" is needed, also the administrator authority and the user that 
> ordinarily lacks it.
> 
> Of course, there is a Vista-bug here, but, also, surely a MySQL-bug. Where is 
> there a list of known bugs?



signature.asc
Description: OpenPGP digital signature

Vista crashes

[Hal�sz S�ndor]

I find that, when under Vista the MySQL daemon has been shut down, by giving 
the command
start mysqld -b"%CD%"
in the root directory where MySQL 5.5.8 (the version running on this computer) 
has been stored from an instance of command prompt with administrator authority 
issued by a user that lacks it (like an ordinary user s doing bare "su" in 
Unix) I make Vista crash with the blue-screen message "process or task critical 
to system operation has been terminated or exited".

The "start" is needed, also the administrator authority and the user that 
ordinarily lacks it.

Of course, there is a Vista-bug here, but, also, surely a MySQL-bug. Where is 
there a list of known bugs?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Null Output Problem

[Hal�sz S�ndor]

Generally when one has this problem one joins this query with something from 
which one can get the whole list, something like this:

SELECT identifier, IFNULL(c, 0) AS Good, 
(query with COUNT) AS P RIGHT JOIN table-of-identifiers ON P.identifier = 
table-of-identifiers.identifier

The "c" is the name given "COUNT(*)" in the query with COUNT(*).

This yields at least one row for every one in "table-of-identifiers", whether 
there is a match in "query with COUNT" or not; if not, "c" is NULL, and with 
"IFNULL" that NULL is made 0.

You write "test-taker", but for a field that could be the foregoing 
"identifier" your query contains only "subject_identifier", which does not look 
like a test-taker.

 2011/10/11 12:26 -0600, Jon Forsyth 
I have a problem with the following query:

SELECT subject_identifier, COUNT(*) FROM asr_sentence_score WHERE
total_words = correct_words GROUP BY subject_identifier;

OutPut:

++--+
| subject_identifier | COUNT(*) |
++--+
|   222 |2 |
|   111 |2 |
|   333 |1 |
|   444 |   11 |
|   888 |6 |
|   666 |   25 |
|   777 |2 |
|   555 |   20 |
|   999 |4 |
|   000 |3 |
++--+
10 rows in set (0.00 sec)

The asr_sentence_score table is a list of test results where each row is a
single item(sentence) on the test.  The subject_identifier is unique to the
test taker, and is repeated for each test item.  I was using this query to
compute a count of how many items each test taker scored perfectly (total_words
= correct_words), but I realized that this excludes a test taker who did not
score perfect for any item.  I want to output a '0' for those that did not
score any item perfectly. 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Inconsistent query result.

[Peter Brawley]

On 10/11/2011 12:26 PM, Paul Halliday wrote:

On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley
  wrote:

On 10/11/2011 8:11 AM, Paul Halliday wrote:

I have the following query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

The part that is causing the strange result is probably this:

AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
map2.c_long != 'US')

the value could be 'US' or 'UNITED STATES' depending on user input so
I check both table fields against their input.

On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
5.1.54), 'US' appears in the results.

Is there a better way to write this?

Is this what you mean?

SELECT
  COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip),
map1.
  cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
  signature, signature_id, ip_proto
FROM event
LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' AND
map1.c_long != 'United States'
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' AND
map2.c_long != 'United States'
WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

PB


Is it OK to keep adding to those joins?
Sure. At some point, though, you might consider an intermediate table to 
precompute/hide some of the complexity.


PB

-

In a view there could be say
50 countries. The user can keep on adding more to exclude. So would I
just expand on the ANDs like so:

AND map1.cc !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Tompkins Neil]

Thanks for the information.  One final question in what ways should we use
EXPLAIN EXTENDED statement to help improve our query performance.

On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman wrote:

> - Original Message -
> > From: "Rik Wasmus" 
> >
> > Depends on the data and usage, but probably I'd go for a index(a,b) &
> > index(b,a) if reads heavily outnumber writes.  As index(a) is covered
> > by index(a,b), and index(b) by index(b,a), we don't need to add those,
> > which saves time on modifications.
>
> I'm trying to think of a scenario where index(a) would be beneficial in the
> presence of index(a,b). If both are available, and all else being equal,
> it's likely that the parser will pick the simplest index; but I can't see it
> having a major impact.
>
> Any full prefix of a combined index may be used; so afaik a separate index
> on any full prefix is a waste of diskspace and cycles.
>
> The net conclusion, Neil, is that you actually have to know what you're
> doing :-) Take the time to read the online documentation on mysql.com,
> it's pretty good.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
>
>

Null Output Problem

[Jon Forsyth]

Hello,

I have a problem with the following query:

SELECT subject_identifier, COUNT(*) FROM asr_sentence_score WHERE
total_words = correct_words GROUP BY subject_identifier;

OutPut:

++--+
| subject_identifier | COUNT(*) |
++--+
|   222 |2 |
|   111 |2 |
|   333 |1 |
|   444 |   11 |
|   888 |6 |
|   666 |   25 |
|   777 |2 |
|   555 |   20 |
|   999 |4 |
|   000 |3 |
++--+
10 rows in set (0.00 sec)

The asr_sentence_score table is a list of test results where each row is a
single item(sentence) on the test.  The subject_identifier is unique to the
test taker, and is repeated for each test item.  I was using this query to
compute a count of how many items each test taker scored perfectly (total_words
= correct_words), but I realized that this excludes a test taker who did not
score perfect for any item.  I want to output a '0' for those that did not
score any item perfectly.  My best guess at a solution would be to revise
the WHERE clause to something like this:

WHERE [total_words = correct_words] OR [COUNT(total_words = correct_words) =
0]

but this is bad syntax.  I put the brackets there for readability.

Thanks,

Jon

Re: Inconsistent query result.

[Johan De Meersman]

- Original Message -
> From: "Paul Halliday" 
> 
> Is it OK to keep adding to those joins? In a view there could be say
> 50 countries. The user can keep on adding more to exclude. So would I
> just expand on the ANDs like so:
> 
> AND map1.cc !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ?

Yes, although "not in ()" is probably going to look a bit more manageable.

I'm also not a big fan of putting where predicates in the join clause - it's a 
dirty trick - although in some cases it does shave quite a bit off the 
execution time.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Inconsistent query result.

[Paul Halliday]

On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley
 wrote:
> On 10/11/2011 8:11 AM, Paul Halliday wrote:
>>
>> I have the following query:
>>
>> SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
>> INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
>> dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
>> mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
>> ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
>> 03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
>> 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
>> src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
>> ORDER BY maxTime DESC LIMIT 5000
>>
>> The part that is causing the strange result is probably this:
>>
>> AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
>> map2.c_long != 'US')
>>
>> the value could be 'US' or 'UNITED STATES' depending on user input so
>> I check both table fields against their input.
>>
>> On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
>> 5.1.54), 'US' appears in the results.
>>
>> Is there a better way to write this?
>
> Is this what you mean?
>
> SELECT
>  COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip),
> map1.
>  cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
>  signature, signature_id, ip_proto
> FROM event
> LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' AND
> map1.c_long != 'United States'
> LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' AND
> map2.c_long != 'United States'
> WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
> GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
> ORDER BY maxTime DESC LIMIT 5000
>
> PB
>

Is it OK to keep adding to those joins? In a view there could be say
50 countries. The user can keep on adding more to exclude. So would I
just expand on the ANDs like so:

AND map1.cc !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Initial install instructions for mac lion/macports

[Tim Johnson]

* Brandon Phelps  [111011 07:43]:
> Tim,
 
> Just a reminder, as I am not sure if it is documented or not;
> After you get MySQL up and running via the DMG package be sure to
> install the System Preferences pane (it didn't use to install by
> default, not sure if it does now) which should be one of the icons
> you get when the DMG first opens, if I recall.  Once you get the
> system preferences pane installed you can access it and enable the
> Startup option so that MySQL starts whenever the system is booted.
> I am not sure what your requirements are but without the
> previously mentioned steps, if your machine loses power while you
> are away and is subsequently rebooted, you will not be able to
> access the database until you manually start the service.
 
> Hope this helps.
 Brandon, thanks a lot for that tip. I appreciate it. 

-- 
Tim 
tim at tee jay forty nine dot com or akwebsoft dot com
http://www.akwebsoft.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Initial install instructions for mac lion/macports

[Brandon Phelps]

Tim,

Just a reminder, as I am not sure if it is documented or not;  After you get 
MySQL up and running via the DMG package be sure to install the System 
Preferences pane (it didn't use to install by default, not sure if it does now) 
which should be one of the icons you get when the DMG first opens, if I recall. 
 Once you get the system preferences pane installed you can access it and 
enable the Startup option so that MySQL starts whenever the system is booted.  
I am not sure what your requirements are but without the previously mentioned 
steps, if your machine loses power while you are away and is subsequently 
rebooted, you will not be able to access the database until you manually start 
the service.

Hope this helps.

-Brandon

On 10/11/2011 11:29 AM, Tim Johnson wrote:

* Tim Johnson  [111010 15:37]:

I familiar with mysql on linux, as of late - ubuntu.
I am installing mysql on a mac mini, with the Lion - osx 10.7
operating system.

mysql-server was downloaded and built with macports.
Following instructions included has not resulted in a successful
startup.

  :) Last night before turning in, I thought that maybe I should have
  investigated the DMG option. It is clear from the respondents that
  I should do this.
  Thanks a lot. I will uninstall via macports and use the package as
  recommended.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Beginner question

[Biz-comm]

Thanks for the pointer. Digging out reference books to learn how to do a join.

:-)



On Oct 11, 2011, at 11:23 AM, Johan De Meersman wrote:

> Which probably means not so much to someone who doesn't even know what a join 
> is :-)
> 
> Have a look at http://www.w3schools.com/sql/sql_join.asp .

Regards,

Patrice Olivier-Wilson
828-628-0500
http://Biz-comm.com
b...@biz-comm.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Initial install instructions for mac lion/macports

[Tim Johnson]

* Tim Johnson  [111010 15:37]:
> I familiar with mysql on linux, as of late - ubuntu.
> I am installing mysql on a mac mini, with the Lion - osx 10.7
> operating system.
> 
> mysql-server was downloaded and built with macports.
> Following instructions included has not resulted in a successful
> startup.
 :) Last night before turning in, I thought that maybe I should have
 investigated the DMG option. It is clear from the respondents that
 I should do this. 
 Thanks a lot. I will uninstall via macports and use the package as
 recommended.
-- 
Tim 
tim at tee jay forty nine dot com or akwebsoft dot com
http://www.akwebsoft.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Beginner question

[Johan De Meersman]

- Original Message -
> From: "Andrew Moore" 
> 
> Be mindful that your query is using 2 tables and 'SELECT *'.

Which probably means not so much to someone who doesn't even know what a join 
is :-)

Have a look at http://www.w3schools.com/sql/sql_join.asp .


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Beginner question

[Andrew Moore]

Hey, welcome to the lists,

Be mindful that your query is using 2 tables and 'SELECT *'.



On Tue, Oct 11, 2011 at 4:11 PM, Biz-comm  wrote:

> I am trying to write a query for a web page that shows a list of users in a
> particular group.
>
> There are 3 tables:
> pm1_users that uses UserID
> pm1_groupsubscriptions that uses UserID and GroupID
> pm1_mailingroups that uses GroupID
>
> So I want to show all the users that belong to a specific mailingroup
>
> SELECT *
> FROM pm1_groupsubscriptions, pm1_users
> WHERE GroupID = 10
>
> (10 = one of the mailingroups)
>
> That isn't enough to get there. That shows all uses.
>
> Thanks for any assistance.
>
>
> Regards,
>
> Patrice Olivier-Wilson
> 828-628-0500
> http://Biz-comm.com
> b...@biz-comm.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
>
>

Re: Initial install instructions for mac lion/macports

[Brandon Phelps]

I only use ports for a very few number of apps, such as irssi, newer versions 
of perl, etc.

I was recommending the OP use the DMG installer, as it comes with everything 
necessary, including startup scripts.

-Brandon

On 10/11/2011 10:05 AM, Vladislav Geller wrote:

Hi Brandon,

MacPorts is practically dead. I tihnk you will have more luck with
http://mxcl.github.com/homebrew/

I have not built mysql with it though on lion.

regards,
Vladislav

On Tue, Oct 11, 2011 at 3:56 PM, Brandon Phelps  wrote:


Is there any reason why you are using ports and not the native 64-bit DMG
from mysql.com?

http://www.mysql.com/**downloads/mysql/#downloads

I run the latest version (5.5.15) on my macbook running lion and the
install goes without a hitch.

Brandon


On 10/10/2011 07:34 PM, Tim Johnson wrote:


I familiar with mysql on linux, as of late - ubuntu.
I am installing mysql on a mac mini, with the Lion - osx 10.7
operating system.

mysql-server was downloaded and built with macports.
Following instructions included has not resulted in a successful
startup.
I have recorded error messages, but it may suffice if I were pointed
to initial setup instructions for Mac Lion. So far, google has not
edified me.

TIA



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?**unsub=vladislav.geller@*
*vincorex.ch






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Initial install instructions for mac lion/macports

[Vladislav Geller]

Hi Brandon,

MacPorts is practically dead. I tihnk you will have more luck with
http://mxcl.github.com/homebrew/

I have not built mysql with it though on lion.

regards,
Vladislav

On Tue, Oct 11, 2011 at 3:56 PM, Brandon Phelps  wrote:

> Is there any reason why you are using ports and not the native 64-bit DMG
> from mysql.com?
>
> http://www.mysql.com/**downloads/mysql/#downloads
>
> I run the latest version (5.5.15) on my macbook running lion and the
> install goes without a hitch.
>
> Brandon
>
>
> On 10/10/2011 07:34 PM, Tim Johnson wrote:
>
>> I familiar with mysql on linux, as of late - ubuntu.
>> I am installing mysql on a mac mini, with the Lion - osx 10.7
>> operating system.
>>
>> mysql-server was downloaded and built with macports.
>> Following instructions included has not resulted in a successful
>> startup.
>> I have recorded error messages, but it may suffice if I were pointed
>> to initial setup instructions for Mac Lion. So far, google has not
>> edified me.
>>
>> TIA
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?**unsub=vladislav.geller@*
> *vincorex.ch
>
>

Re: Initial install instructions for mac lion/macports

[Brandon Phelps]

Is there any reason why you are using ports and not the native 64-bit DMG from 
mysql.com?

http://www.mysql.com/downloads/mysql/#downloads

I run the latest version (5.5.15) on my macbook running lion and the install 
goes without a hitch.

Brandon

On 10/10/2011 07:34 PM, Tim Johnson wrote:

I familiar with mysql on linux, as of late - ubuntu.
I am installing mysql on a mac mini, with the Lion - osx 10.7
operating system.

mysql-server was downloaded and built with macports.
Following instructions included has not resulted in a successful
startup.
I have recorded error messages, but it may suffice if I were pointed
to initial setup instructions for Mac Lion. So far, google has not
edified me.

TIA


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Johan De Meersman]

- Original Message -
> From: "Rik Wasmus" 
> 
> Depends on the data and usage, but probably I'd go for a index(a,b) &
> index(b,a) if reads heavily outnumber writes.  As index(a) is covered
> by index(a,b), and index(b) by index(b,a), we don't need to add those,
> which saves time on modifications.

I'm trying to think of a scenario where index(a) would be beneficial in the 
presence of index(a,b). If both are available, and all else being equal, it's 
likely that the parser will pick the simplest index; but I can't see it having 
a major impact.

Any full prefix of a combined index may be used; so afaik a separate index on 
any full prefix is a waste of diskspace and cycles.

The net conclusion, Neil, is that you actually have to know what you're doing 
:-) Take the time to read the online documentation on mysql.com, it's pretty 
good.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Inconsistent query result.

[Peter Brawley]

On 10/11/2011 8:11 AM, Paul Halliday wrote:

I have the following query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

The part that is causing the strange result is probably this:

AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
map2.c_long != 'US')

the value could be 'US' or 'UNITED STATES' depending on user input so
I check both table fields against their input.

On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
5.1.54), 'US' appears in the results.

Is there a better way to write this?

Is this what you mean?

SELECT
  COUNT(signature) AS count, MAX(timestamp) AS maxTime, 
INET_NTOA(src_ip), map1.

  cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
  signature, signature_id, ip_proto
FROM event
LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' 
AND map1.c_long != 'United States'
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' 
AND map2.c_long != 'United States'

WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

PB

-



Thanks.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Inconsistent query result.

[Paul Halliday]

I have the following query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

The part that is causing the strange result is probably this:

AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
map2.c_long != 'US')

the value could be 'US' or 'UNITED STATES' depending on user input so
I check both table fields against their input.

On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
5.1.54), 'US' appears in the results.

Is there a better way to write this?

Thanks.

-- 
Paul Halliday
http://www.squertproject.org/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Rik Wasmus]

> In this instance would you create four indexes key(a) key(b) key(a,b) key
> (b,a) ? Or is the decision based on the query response time ?

Depends on the data and usage, but probably I'd go for a index(a,b) & 
index(b,a) if reads heavily outnumber writes.  As index(a) is covered by 
index(a,b), and index(b) by index(b,a), we don't need to add those, which 
saves time on modifications.
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Neil Tompkins]

In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) 
? Or is the decision based on the query response time ?

On 11 Oct 2011, at 13:40, Rik Wasmus  wrote:

>> Next question. If you have the two separate indexes and then do two
>> queries, one for a and one for b. If you then get a list of unique id's
>> of both, would it be faster to create an intersection yourself rather
>> than have the server do the legwork?
> 
> If you only have 2 unrelated indexes on a & b, it depends on the data, the 
> distribution of values, etc. No single answer here, test with your data and 
> you'll have the results.
> 
> If you need it often, I'd go for the combined index & let MySQL do the work, 
> which is probably fastest. 
> -- 
> Rik Wasmus
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Rik Wasmus]

> Next question. If you have the two separate indexes and then do two
> queries, one for a and one for b. If you then get a list of unique id's
> of both, would it be faster to create an intersection yourself rather
> than have the server do the legwork?

If you only have 2 unrelated indexes on a & b, it depends on the data, the 
distribution of values, etc. No single answer here, test with your data and 
you'll have the results.

If you need it often, I'd go for the combined index & let MySQL do the work, 
which is probably fastest. 
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Alex Schaft]

On 2011/10/11 02:30 PM, Alex Schaft wrote:

On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no 
difference ?

They DO.

See it as lookup table which starts with 'a' in the first case, and 
'b'  in the
second one. Looking for anything that matches 'b' for an index (a,b) 
requires
a full scan as you don't know 'a', likewise searching for 'a' in an 
index
(b,a) requires a full scan. See it as looking through a phonebook 
trying to
locate someone by first- rather then lastname. It's in there, just 
not easily

accessible.

However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) 
then with

only an index on (a).

Johan was trying to explain this distinction:

- index (a,b) is good for searches on ONLY a  or BOTH a&  b, but bad 
for ONLY

b
- index (b,a) is good for searches on ONLY b  or BOTH a&  b, but bad 
for ONLY

a
- index (a)&  index (b) is good for searches on ONLY b  or ONLY a, 
and is
suboptimal for searching for BOTH a,b (although, faster then no 
index, but the

query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two 
queries, one for a and one for b. If you then get a list of unique 
id's of both, would it be faster to create an intersection yourself 
rather than have the server do the legwork?





Then there's index merge optimizations too I suppose

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Alex Schaft]

On 2011/10/11 02:22 PM, Rik Wasmus wrote:

Just to clarify having key indexes of (a,b) or (b,a) have no difference ?

They DO.

See it as lookup table which starts with 'a' in the first case, and 'b'  in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.

However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).

Johan was trying to explain this distinction:

- index (a,b) is good for searches on ONLY a  or BOTH a&  b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b  or BOTH a&  b, but bad for ONLY
a
- index (a)&  index (b) is good for searches on ONLY b  or ONLY a, and is
suboptimal for searching for BOTH a,b (although, faster then no index, but the
query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two 
queries, one for a and one for b. If you then get a list of unique id's 
of both, would it be faster to create an intersection yourself rather 
than have the server do the legwork?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Rik Wasmus]

> Just to clarify having key indexes of (a,b) or (b,a) have no difference ?

They DO.

See it as lookup table which starts with 'a' in the first case, and 'b'  in the 
second one. Looking for anything that matches 'b' for an index (a,b) requires 
a full scan as you don't know 'a', likewise searching for 'a' in an index 
(b,a) requires a full scan. See it as looking through a phonebook trying to 
locate someone by first- rather then lastname. It's in there, just not easily 
accessible.

However, if you have an index on (a,b) and DO know which 'a' you want 
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with 
only an index on (a).

Johan was trying to explain this distinction:

- index (a,b) is good for searches on ONLY a  or BOTH a & b, but bad for ONLY 
b
- index (b,a) is good for searches on ONLY b  or BOTH a & b, but bad for ONLY 
a
- index (a) & index (b) is good for searches on ONLY b  or ONLY a, and is 
suboptimal for searching for BOTH a,b (although, faster then no index, but the 
query optimizer has to choose which index to use, can't use both).
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Arthur Fuller]

The difference is based on the relative frequency of queries where a is
important vs. where b is important. Either way, what will happen is that the
index scan will isolate the first item mentioned, then scan the result set
to isolate the second term.

e.g.

SELECT * FROM someTable WERE a = someValue and b = someOtherValue

Step one isolates the matching "a" values.
Step two walks through that resultset and examines each value of b.

Since the values of b are already in memory, all it has to do is examine the
index keys to find the matches of b. No additional disk read is required.

Arthur

Re: Index question

[Neil Tompkins]

Just to clarify having key indexes of (a,b) or (b,a) have no difference ?

On 11 Oct 2011, at 09:36, Johan De Meersman  wrote:

> - Original Message -
>> From: "Alex Schaft" 
>> 
>> If you have a table with columns A & B, and might do a where on A or
>> B, or an order by A, B, would single column indexes on A and B suffice
>> or would performance on the order by query be improved by an index on
>> A,B?
> 
> Depends on usage :-)
> 
> key (a, b) is good for "where a=.." or "where a=.. and b=.."
> key (b, a) is good for "where b=.." or "where b=.. and a=.."
>  (note that the sequence of a and b in the where clause is not important)
> key (a), key (b) is good for "where a=.." or "where b=.." but will only use 
> one index for "where a=.. and b=..".
> 
> I think work is ongoing on having the parser use multiple indices, but I'm 
> not sure where that's at.
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Index question

[Johan De Meersman]

- Original Message -
> From: "Alex Schaft" 
> 
> If you have a table with columns A & B, and might do a where on A or
> B, or an order by A, B, would single column indexes on A and B suffice
> or would performance on the order by query be improved by an index on
> A,B?

Depends on usage :-)

key (a, b) is good for "where a=.." or "where a=.. and b=.."
key (b, a) is good for "where b=.." or "where b=.. and a=.."
  (note that the sequence of a and b in the where clause is not important)
key (a), key (b) is good for "where a=.." or "where b=.." but will only use one 
index for "where a=.. and b=..".

I think work is ongoing on having the parser use multiple indices, but I'm not 
sure where that's at.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why the same command have different results when it is in a bash script and when it is not?

[Johan De Meersman]

I don't have a direct answer for you, just some thoughts:
 * traditionally, "localhost" is thought of as an alias for 127.0.0.1
 * mysql however, tends to not interpret it like when connecting to the local 
server and instead tries to connect to the socket
 * to force mysql client to connect over tcp/ip instead, use -h 127.0.0.1

It's possible that the execution environment of a script is subtly different 
and causes the client to interpret it the other way, but I've no idea what the 
exact difference would be.


- Original Message -
> From: "Peng Yu" 
> To: mysql@lists.mysql.com
> Sent: Monday, 10 October, 2011 12:19:20 AM
> Subject: Why the same command have different results when it is in a bash 
> script and when it is not?
> 
> Hi,
> 
> It is seems strange to me why the same command "mysql -hlocalhost
> -uxxx" when it is run from the command line, it will show an error.
> When it is run in a bash script, it runs fine. I guess this might be
> described somewhere, but I'm yet to find the answer. Does anybody
> know
> why this happens and how to make the command line version working?
> 
> ~$ mysql -hlocalhost -uxxx
> ERROR 1045 (28000): Access denied for user 'xxx'@'localhost' (using
> password: YES)
> ~$ cat `which mysqllocalhostxxx.sh `
> #!/usr/bin/env bash
> 
> mysql -hlocalhost -uxxx
> ~$  mysqllocalhostxxx.sh
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 101
> Server version: 5.5.9 Source distribution
> 
> Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights
> reserved.
> 
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective
> owners.
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
> 
> mysql>
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Index question

[Alex Schaft]

If you have a table with columns A & B, and might do a where on A or B, 
or an order by A, B, would single column indexes on A and B suffice or 
would performance on the order by query be improved by an index on A,B?


Thanks


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org