Re: Can a table be copied across the DBs?

[Chris]

[EMAIL PROTECTED] wrote:

Hi All,

I want to copy a table (along with its contents) from one
database to another database.

In the destination database the table doesn't exist. Please let me know,
if there is any way to do it.


mysqldump -u  -p original_databasename | mysql -u  
-p new_databasename


check mysqldump --help for options.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can a table be copied across the DBs?

[ravi.karatagi]

Hi All,

I want to copy a table (along with its contents) from one
database to another database.

In the destination database the table doesn't exist. Please let me know,
if there is any way to do it.



Regards,

Ravi K






The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email.

www.wipro.com

RE: 1 to many relationship

[Peter Lauri]

This was very good reading. And we all learn something new everyday. What
you are writing makes so much sense.

This also comes from me relying on that all things said on this list is
true, and that all members do know what they write before they write it. I
try to never post a reply to anyone unless I'm convinced that I am right.

So I learned a lesson. The big lesson learned was not maybe the one about
how a database optimizes a question, but rather something else.

/Peter





-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 18, 2006 1:14 AM
To: mysql@lists.mysql.com
Cc: Chris; Peter Lauri
Subject: Re: 1 to many relationship

Peter Lauri wrote:
 > Is there not a better way to do that? What will happen there is that a
large
 > result set will be created because when you just do "select * from
 > customers c, issues i, customer_issues ci" it will be like the inner
product
 > from all these tables, and then just choosing the right ones.
 >
 > If the table C have 1000 records, issues 5000 and customer_issues 15000
you
 > would end up with a 75,000,000,000 rows large results set, that would not
be
 > so sweet, would it?

Peter Lauri wrote:
 > Yes, it cuts it down to that number of records in the end, so the final
 > result set will just be a few rows that match the 'WHERE'. But the
internal
 > process of MySQL do merge all tables and then chooses the records that
 > matches the 'WHERE' clause.

No database would work very well if that were the case.  You are essentially

asserting that mysql has no optimizer.  That just isn't so.  The job of the 
optimizer is to devise a plan to execute the query in a way that will
examine 
the fewest possible rows.  Eliminating rows before looking at them is always

preferable to eliminating them afterwards.

For example, given the query

   SELECT *
   FROM customers c
   JOIN customer_issues ci ON c.customerid = ci.customerid
   JOIN issues i on ci.issueid = i.issueid
   WHERE c.customerid = 13;

mysql will use the index on customers.customerid to select the 1 row with 
customerid = 13, it will then use the index on customer_issues.customerid to

find matching rows in customer_issues, then finally it will use the index on

issues.issueid to find matching rows in issues.  This is easily verified
using 
EXPLAIN:

   EXPLAIN SELECT *
   FROM customers c
   JOIN customer_issues ci ON c.customerid = ci.customerid
   JOIN issues i on ci.issueid = i.issueid
   WHERE c.customerid = 13;

   +-+---++-+-+--+
   | select_type | table | type   | key | ref | rows |
   +-+---++-+-+--+
   | SIMPLE  | c | const  | PRIMARY | const   |1 |
   | SIMPLE  | ci| ref| PRIMARY | const   |4 |
   | SIMPLE  | i | eq_ref | PRIMARY | test.ci.issueid |1 |
   +-+---++-+-+--+
   3 rows in set (0.01 sec)

(Note that I've pared the output of EXPLAIN down to a few relevant columns.)

The "rows" column tells the story.  Mysql plans to use the primary key to
find 
the 1 matching row in customers, then use the primary key to find the 4
matching 
rows in customer_issues for that 1 customer, then use the primary key to
find 
the 1 matching row in issues for each row found in customer_issues.  That
is, it 
expects to produce 1x4x1 = 4 rows, not 'size of c' x 'size of ci' x 'size of
i' 
rows!

You might want to read the optimization section of the manual for more on
the 
subject .

Chris wrote:
 > I don't know enough about mysql internals to debate that so I'll take
 > your word for it.
 >
 > 'Explain' doesn't give enough information about what happens behind the
 > scenes so I'm not sure how to prove/disprove that and I don't know of
 > any tools that would show you that (if there is let me know!).
 >
 > Having said all of that I've never had a problem doing it the way I
 > mentioned.. ;)

EXPLAIN is documented in the manual 
.

Michael

-- 
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: AW: AW: find in list

[Michael Stassen]

Charlie Schaubmair wrote:

Hello Michael,

thx, I know normalisation.

BUT normalisation isn't always the best (fastest) way to store, or select
the datas, this is why I don't use it most time.
Often I'm testing my projects with normalisation and without and my last
very big problem with big select statements is very fast without
normalisation and with normalisation it was very slow.

br
Charlie


The point of properly normalized and indexed tables is that they almost
always produce more efficient queries. There are exceptions, of course, but
they are just that, exceptions, and I assure you, this is not one of those
times. Finding rows with a particular attribute was made difficult by the
lack of normalization. More to the point, there is no chance that

  SELECT *
  FROM MyTable_attributes
  WHERE FIND_IN_SET(1, someFieldInMyTable);

with the denormalized design is close to as fast as

  SELECT m.*
  FROM MyTable m
  JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
  WHERE ma.attribute = 1;

with the normalized design, as long as the proper indexes are present.  The
former is a guaranteed full-table scan, while the latter is a simple index look 
up.

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 to many relationship

[Michael Stassen]

Chris wrote:

> Hmm. Must be a recent change, I've had problems with that in the past.
>
> Thanks for the pointers.

You're welcome.  I'm not sure which part you think is a recent change. 
Certainly, mysql 5 with its demotion of the precedence of the implicit join is 
recent.  Perhaps it depends on your definition of recent.  According to the 
manual , putting the join 
condition in an ON clause was first supported for INNER JOIN as of 3.23.17, and 
for JOIN starting with 4.0.11.  Is that what you mean?


Chris wrote:


EXPLAIN is documented in the manual 
.


So it is, but it doesn't show me the decisions it makes. It shows me 
whether it will use an index or not, it won't show me how it puts the 
whole thing together.


Well, I think that information is there, unless I'm mistaking what you mean. 
Mysql joins the tables in the order given in the explain output.  It shows 
whether or not an index is being used, how many rows are predicted to be 
examined, and how the rows will be sorted.



Compare to the postgres one:

http://www.designmagick.com/article/23/page/5

(yes I did write that article so I am biased)

which tells me what's going on at each step of the decision process.

Whether you find that useful or not - *shrug* - I however find it very 
useful and wish the mysql explain was as good as that.


I've not used postgresql, so I will make no claim as to the relative merits of 
each system's EXPLAIN.  It appears you're in a better position to make 
comparisons.  I'll just say that it takes some practice/experience to really get 
what mysql's EXPLAIN is telling you, and I gather from your article that the 
same could be said of postgresql's EXPLAIN.  At least, I couldn't tell just from 
your article what useful info postgresql's EXPLAIN gives that mysql's does not. 
 I wonder if it is possible that what you are looking for is hidden in mysql's 
EXPLAIN output in a way that isn't apparent to you.  If you think that might be 
the case, and you told us what you're looking for, someone could say if it's 
there or not.  On the other hand, you may be certain that's not the case.  Then 
I'd be curious to hear about it.


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Log files

[Chris]

bruce wrote:

chris...

hate to tell you.. .but you can have the log files set as i described..

the real issue was due to permission issues on the dir/file for the mysql


lol.. i'm not having much luck answering questions lately ;)

Thanks for letting us know about the fix.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tables/sum

[Chris]

Peter South wrote:

Ok here is what I'm trying to do.

 Table 1  Table 2   
   Table 3
Column1(id)  Column2 (score)  Column1(id)   Column2(score)Column1(id)   Column2(name)  
1101   151Fred
210   2   (no score)2   Jim  
3  (no score) 315   3Joe




The result I want is 
Fred scored 25 (or) 
Joe scored 15 (or)

Jim scored 10.
Peter


Like the others said, join your tables and add them up:

select t1.id as id, sum(t1.score + t2.score + t3.score) as total from 
table1 t1, table2 t2, table3 t3 where t1.id=t2.id and t1.id=t3.id group 
by t1.id;



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Log files

[Chris]

bruce wrote:

hi...

i changed the mysql my.cnf to provide log files. i restarted mysql, but i
don't see the log files...

any idea as to what i may have missed. i'm running FC4, mysql 4.1.

the my.cnf is:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=1000
set-variable = log-error=/var/log/mysql_err.log
set-variable = log=/var/log/mysql_query.log


Logs aren't defined that way, they are simply:

log = /path/to/log

(ie without the 'set-variable' at the start).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 to many relationship

[Chris]

Michael Stassen wrote:

Chris wrote:

 > select *
 > from customers c, issues i, customer_issues ci
 > where c.customerid=ci.customerid
 >  AND ci.issueid=i.issueid;

Chris wrote:

 > That query should be the same as this one:
 > select *
 > from customers c
 > inner join customer_issues ci on (c.customerid=ci.customerid)
 > inner join issues i on (ci.issueid=i.issueid);
 >
 > I don't like writing my queries that way because the order of the joins
 > needs to be very specific and as you add more tables, getting the order
 > wrong can make things slow (and hard to track down).

I think you have that precisely backwards.  Putting the join conditions 
in the WHERE clause makes it easy to accidentally forget one.  Many, 
many "Why isn't this query working?" questions on this list have been 
the result of missing join conditions that became obvious when the query 
was rewritten to explicitly give the join conditions in ON clauses.  As 
you add more tables, the likelihood of making that mistake and the 
difficulty of detecting it increase.


Whether you put it in the WHERE or ON clause, the correct join condition 
is the same.  Like you, I used to write all my joins as implicit joins 
with the join conditions in the WHERE clause (largely becuase most of 
the examples in the manual were written that way).  It worked fine for 
the relatively simple queries I was writing at the time, but as my 
queries became more complex, that format became more and more unwieldy.  
With join conditions for 4 or 5 tables and 3 or 4 row restrictions in 
the WHERE clause, it gets to be a mess.  Faced with that, and seeing the 
problems others have had, I've gradually switched to writing all of my 
joins with explicit join conditions in ON clauses.


I also have no idea what you mean by "getting the order wrong can make 
things slow."  The order in which you write inner joins should have no 
effect on how your query is performed.  The optimizer will, in fact, 
consider each of the possible orderings that produce correct results and 
choose the one it calculates to be the most efficient.  That is,


  SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i ON ci.issueid = i.issueid
  WHERE c.customerid = 13;

is identical to

  SELECT *
  FROM issues i
  JOIN customer_issues ci ON ci.issueid = i.issueid
  JOIN customers c ON c.customerid = ci.customerid
  WHERE c.customerid = 13;

in that mysql will do the same thing to satisfy either one.  This can be 
verified through the use of EXPLAIN.


In short, there is absolutely no speed benefit in putting your join 
conditions in the WHERE clause.  In fact, there is a small inefficiency 
in putting them there, as mysql must then parse the WHERE clause into 
join conditions and row restrictions.  This is very fast, of course, but 
doesn't have to be done if the join conditions are explicitly given in 
ON clauses.


Finally, you will discover that some of your queries which use the 
"comma as implicit join operator" will break when you upgrade to mysql 
5, because starting with mysql 5, implicit joins are given lower 
precedence than explicit joins, as specified by the SQL standard.  See 
the manual for the details:


.


Hmm. Must be a recent change, I've had problems with that in the past.

Thanks for the pointers.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 to many relationship

[Chris]

EXPLAIN is documented in the manual 
.


So it is, but it doesn't show me the decisions it makes. It shows me 
whether it will use an index or not, it won't show me how it puts the 
whole thing together.


Compare to the postgres one:

http://www.designmagick.com/article/23/page/5

(yes I did write that article so I am biased)

which tells me what's going on at each step of the decision process.

Whether you find that useful or not - *shrug* - I however find it very 
useful and wish the mysql explain was as good as that.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tables/sum

[Peter South]

Ok here is what I'm trying to do.

 Table 1  Table 2   
   Table 3
Column1(id)  Column2 (score)  Column1(id)   Column2(score)  
  Column1(id)   Column2(name)  
1101
   151Fred
210   2 
  (no score)2   Jim  
3  (no score) 3
15   3Joe



The result I want is 
Fred scored 25 (or) 
Joe scored 15 (or)
Jim scored 10.
Peter

RE: More query help for user-defined values

[Robert DiFalco]

One simple solution is to denormalize a little and take the refTypeID
column out of StringValue and add it to the Links table. Then I could
have:

SELECT P.*
FROM Person P

 LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1
 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID
 LEFT JOIN StringType  ST1 ON ST1.ID = 1

 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2
 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID
 LEFT JOIN StringType  ST2 ON ST2.ID = 2

WHERE ( SV1.f_val LIKE 'foo' OR
  ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
  AND
  ( SV2.f_val LIKE 'bar' OR
  ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) 


-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 17, 2006 8:43 AM
To: mysql@lists.mysql.com
Subject: More query help for user-defined values

To support user defined properties I have the following tables:

TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue

TABLE StringValue
LONG parentID
LONG typeID
VARCHAR val

Assume the correct indices are in place. Different string value types
can be defined by giving them a new row in StringType. The
StringValue.parentID can be a row from any table that has a LONG
identity column. If a row in the parent table does not have a
corresponding row in StringValue, we want to assume an implicit default
value.

So to return all the Person rows that have either an explicit
StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I
can just execute the following query.

SELECT P.*
FROM Person P
 LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID
= 1
 LEFT JOIN StringType  ST1 ON ST1.ID = 1
 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID
= 2
 LEFT JOIN StringType  ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
  ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
  AND
  ( SV2.f_val LIKE 'bar' OR
  ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
  
The above seems to work fine. I can also get rid of the constant joins
on the type table and just do a subquery for the default value, either
approach works.

Now I want to change things to introduce a ValueLink table so that if
100 rows have the same string value that I don't have to write the
string 100 times. So my table structure changes like this:

TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue

TABLE StringValue
LONG ID PKEY  // the value unique ID
LONG typeID
VARCHAR val

TABLE StringLinks
LONG parentID PKEY
LONG valueID  PKEY

My naive approach to the query was this:

SELECT P.*
FROM Person P
 LEFT JOIN StringLinks L ON P.ID = L.parentID
 LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1
 LEFT JOIN StringType  ST1 ON ST1.ID = 1
 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2
 LEFT JOIN StringType  ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
  ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
  AND
  ( SV2.f_val LIKE 'bar' OR
  ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
  
As you can probably tell this wont work because the two values will now
show up in two different rows so the AND of the search terms will always
fail.

Can anyone think of a clean way to do this or do I have to get rid of
the joins altogether and do a subquery for each StringValue search term?

TIA,

R.





--
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: Tables/sum

[Jay Blanchard]

[snip]

[snip]
Can anyone tell me how to add up values in different tables? For example
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) =3D n
[/snip]

SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id =3D n

The above doesn't work I'm afraid.
[/snip]

Don't be afraid, you must join the tables and there must be a matching
key in each table. http://www.mysql.com/select


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Tables/sum

[Jay Blanchard]

[snip]
Can anyone tell me how to add up values in different tables? For example
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) =3D n
[/snip]

SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id =3D n

The above doesn't work I'm afraid.
[/snip]

Don't be afraid, you must join the tables


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tables/sum

[ddevaudreuil]

How about:

select sum(t1.column1 + t2.column2 +t3.column3) as Columnsum
from 
Table1 t1
inner join Table2 t2 on t1.id=t2.id
inner join Table3 t3 on t2.id=t3.id
where t1.id=n
group by t1.id

This is a rough cut that assumes the id value  in the join  exists in all 
three tables.  If it's missing in any one of the tables, then the query 
will return null.  If that is not okay, then you'd have to do something 
with outer joins...without knowing what you're trying to find it's hard to 
be more specific.

Donna




"Peter South" <[EMAIL PROTECTED]> 
08/17/2006 05:05 PM

To

cc

Subject
Tables/sum






Can anyone tell me how to add up values in different tables? For example 
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) = n
Thanks
Peter
-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



CONFIDENTIALITY NOTICE:This email is intended solely for the person or 
entity to which it is addressed and may contain confidential and/or 
protected health information.  Any duplication, dissemination, action 
taken in reliance upon, or other use of this information by persons or 
entities other than the intended recipient is prohibited and may violate 
applicable laws.  If this email has been received in error, please notify 
the sender and delete the information from your system.  The views 
expressed in this email are those of the sender and may not necessarily 
represent the views of IntelliCare.

Tables/sum

[Peter South]

Can anyone tell me how to add up values in different tables? For example
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) =3D n
[/snip]

SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id =3D n

The above doesn't work I'm afraid.
Peter

Re: Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)

[Eric Braswell]

Is there a reason you cannot upgrade from an early =alpha= version of 5.0 ?

I think you fill find the release versions more stable.

--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA


murthy gandikota wrote:

Hi
  Can someone tell me where in the file system to look for the logs? 
Mysql has been crashing once every hour. Memory is not the issue. This is 
how I start the mysql:
   
  nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql 
--datadir=/usr/
local/mysql/var --user=mysql 
--pid-file=/usr/local/mysql/var/admin.scholasticfun
dinggroup.com.pid --skip-locking --port=3306 
--socket=/var/lib/mysql/mysql.sock 
&
   
  I'd appreciate your help.

  Thanks


-
Do you Yahoo!?
 Get on board. You're invited to try the new Yahoo! Mail Beta.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)

[murthy gandikota]

Looks like I have too many connections to the database. I made sure all the 
connections are closed after use. Hope this is the fix. Anyone please confirm.
  Thanks

murthy gandikota <[EMAIL PROTECTED]> wrote:
  Can someone please help me figure out what's in this mysqld output before 
crashing:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=101
max_connections=100
threads_connected=101
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfffa3ac, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8166bc0
0xb748cdf8
0xb7356f1b
0x815e8af
0x815f55b
0x8166765
0x816a3f5
0x8167b7d
0x8167774
0xb72fb748
0x80e52d1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow 
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

murthy gandikota wrote:
Hi
Can someone tell me where in the file system to look for the logs? 
Mysql has been crashing once every hour. Memory is not the issue. This is 
how I start the mysql:

nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql 
--datadir=/usr/
local/mysql/var --user=mysql 
--pid-file=/usr/local/mysql/var/admin.scholasticfun
dinggroup.com.pid --skip-locking --port=3306 
--socket=/var/lib/mysql/mysql.sock 
&

I'd appreciate your help.
Thanks


-
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail Beta.


-
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail Beta.


-
Do you Yahoo!?
 Get on board. You're invited to try the new Yahoo! Mail Beta.

RE: Tables/sum

[Jay Blanchard]

[snip]
Can anyone tell me how to add up values in different tables? For example
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) = n
[/snip]

SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id = n

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tables/sum

[Peter South]

Can anyone tell me how to add up values in different tables? For example 
Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) = n
Thanks
Peter

Re: Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)

[murthy gandikota]

Can someone please help me figure out what's in this mysqld output before 
crashing:
  mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
  key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=101
max_connections=100
threads_connected=101
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
  thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfffa3ac, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8166bc0
0xb748cdf8
0xb7356f1b
0x815e8af
0x815f55b
0x8166765
0x816a3f5
0x8167b7d
0x8167774
0xb72fb748
0x80e52d1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow 
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

murthy gandikota <[EMAIL PROTECTED]> wrote:
  Hi
Can someone tell me where in the file system to look for the logs? 
Mysql has been crashing once every hour. Memory is not the issue. This is 
how I start the mysql:

nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql 
--datadir=/usr/
local/mysql/var --user=mysql 
--pid-file=/usr/local/mysql/var/admin.scholasticfun
dinggroup.com.pid --skip-locking --port=3306 
--socket=/var/lib/mysql/mysql.sock 
&

I'd appreciate your help.
Thanks


-
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail Beta.


-
Do you Yahoo!?
 Get on board. You're invited to try the new Yahoo! Mail Beta.

Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)

[murthy gandikota]

Hi
  Can someone tell me where in the file system to look for the logs? 
Mysql has been crashing once every hour. Memory is not the issue. This is 
how I start the mysql:
   
  nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql 
--datadir=/usr/
local/mysql/var --user=mysql 
--pid-file=/usr/local/mysql/var/admin.scholasticfun
dinggroup.com.pid --skip-locking --port=3306 
--socket=/var/lib/mysql/mysql.sock 
&
   
  I'd appreciate your help.
  Thanks


-
Do you Yahoo!?
 Get on board. You're invited to try the new Yahoo! Mail Beta.

Re: 1 to many relationship

[Michael Stassen]

Peter Lauri wrote:
> Is there not a better way to do that? What will happen there is that a large
> result set will be created because when you just do "select * from
> customers c, issues i, customer_issues ci" it will be like the inner product
> from all these tables, and then just choosing the right ones.
>
> If the table C have 1000 records, issues 5000 and customer_issues 15000 you
> would end up with a 75,000,000,000 rows large results set, that would not be
> so sweet, would it?

Peter Lauri wrote:
> Yes, it cuts it down to that number of records in the end, so the final
> result set will just be a few rows that match the 'WHERE'. But the internal
> process of MySQL do merge all tables and then chooses the records that
> matches the 'WHERE' clause.

No database would work very well if that were the case.  You are essentially 
asserting that mysql has no optimizer.  That just isn't so.  The job of the 
optimizer is to devise a plan to execute the query in a way that will examine 
the fewest possible rows.  Eliminating rows before looking at them is always 
preferable to eliminating them afterwards.


For example, given the query

  SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i on ci.issueid = i.issueid
  WHERE c.customerid = 13;

mysql will use the index on customers.customerid to select the 1 row with 
customerid = 13, it will then use the index on customer_issues.customerid to 
find matching rows in customer_issues, then finally it will use the index on 
issues.issueid to find matching rows in issues.  This is easily verified using 
EXPLAIN:


  EXPLAIN SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i on ci.issueid = i.issueid
  WHERE c.customerid = 13;

  +-+---++-+-+--+
  | select_type | table | type   | key | ref | rows |
  +-+---++-+-+--+
  | SIMPLE  | c | const  | PRIMARY | const   |1 |
  | SIMPLE  | ci| ref| PRIMARY | const   |4 |
  | SIMPLE  | i | eq_ref | PRIMARY | test.ci.issueid |1 |
  +-+---++-+-+--+
  3 rows in set (0.01 sec)

(Note that I've pared the output of EXPLAIN down to a few relevant columns.)

The "rows" column tells the story.  Mysql plans to use the primary key to find 
the 1 matching row in customers, then use the primary key to find the 4 matching 
rows in customer_issues for that 1 customer, then use the primary key to find 
the 1 matching row in issues for each row found in customer_issues.  That is, it 
expects to produce 1x4x1 = 4 rows, not 'size of c' x 'size of ci' x 'size of i' 
rows!


You might want to read the optimization section of the manual for more on the 
subject .


Chris wrote:
> I don't know enough about mysql internals to debate that so I'll take
> your word for it.
>
> 'Explain' doesn't give enough information about what happens behind the
> scenes so I'm not sure how to prove/disprove that and I don't know of
> any tools that would show you that (if there is let me know!).
>
> Having said all of that I've never had a problem doing it the way I
> mentioned.. ;)

EXPLAIN is documented in the manual 
.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 to many relationship

[Michael Stassen]

Chris wrote:

> select *
> from customers c, issues i, customer_issues ci
> where c.customerid=ci.customerid
>  AND ci.issueid=i.issueid;

Chris wrote:

> That query should be the same as this one:
> select *
> from customers c
> inner join customer_issues ci on (c.customerid=ci.customerid)
> inner join issues i on (ci.issueid=i.issueid);
>
> I don't like writing my queries that way because the order of the joins
> needs to be very specific and as you add more tables, getting the order
> wrong can make things slow (and hard to track down).

I think you have that precisely backwards.  Putting the join conditions in the 
WHERE clause makes it easy to accidentally forget one.  Many, many "Why isn't 
this query working?" questions on this list have been the result of missing join 
conditions that became obvious when the query was rewritten to explicitly give 
the join conditions in ON clauses.  As you add more tables, the likelihood of 
making that mistake and the difficulty of detecting it increase.


Whether you put it in the WHERE or ON clause, the correct join condition is the 
same.  Like you, I used to write all my joins as implicit joins with the join 
conditions in the WHERE clause (largely becuase most of the examples in the 
manual were written that way).  It worked fine for the relatively simple queries 
I was writing at the time, but as my queries became more complex, that format 
became more and more unwieldy.  With join conditions for 4 or 5 tables and 3 or 
4 row restrictions in the WHERE clause, it gets to be a mess.  Faced with that, 
and seeing the problems others have had, I've gradually switched to writing all 
of my joins with explicit join conditions in ON clauses.


I also have no idea what you mean by "getting the order wrong can make things 
slow."  The order in which you write inner joins should have no effect on how 
your query is performed.  The optimizer will, in fact, consider each of the 
possible orderings that produce correct results and choose the one it calculates 
to be the most efficient.  That is,


  SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i ON ci.issueid = i.issueid
  WHERE c.customerid = 13;

is identical to

  SELECT *
  FROM issues i
  JOIN customer_issues ci ON ci.issueid = i.issueid
  JOIN customers c ON c.customerid = ci.customerid
  WHERE c.customerid = 13;

in that mysql will do the same thing to satisfy either one.  This can be 
verified through the use of EXPLAIN.


In short, there is absolutely no speed benefit in putting your join conditions 
in the WHERE clause.  In fact, there is a small inefficiency in putting them 
there, as mysql must then parse the WHERE clause into join conditions and row 
restrictions.  This is very fast, of course, but doesn't have to be done if the 
join conditions are explicitly given in ON clauses.


Finally, you will discover that some of your queries which use the "comma as 
implicit join operator" will break when you upgrade to mysql 5, because starting 
with mysql 5, implicit joins are given lower precedence than explicit joins, as 
specified by the SQL standard.  See the manual for the details:


.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with using "IN" for a sub-query statement

[Michael Stassen]

Chris wrote:

Chris White wrote:

On Monday 07 August 2006 12:13 pm, William DeMasi wrote:

I meant to have it say :

"select * from c2iedm_dev2.act where act_id IN (select obj_act_id from
c2iedm_dev2.act_functl_assoc where subj_act_id =24);"


What does the output of (the subselect):

select obj_act_id
from c2iedm_dev2.act_functl_assoc
where subj_act_id =24;

show?

Sorry about that, typo on my part, but I have tried it as above and I 
get

the error I mentioned.


What version of mysql are you using?

select version();

subselects don't work for versions before v5.


You are right to inquire about version, but subselects are supported in mysql 
starting with 4.1.


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

More query help for user-defined values

[Robert DiFalco]

To support user defined properties I have the following tables:

TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue

TABLE StringValue
LONG parentID
LONG typeID
VARCHAR val

Assume the correct indices are in place. Different string value types
can be defined by giving them a new row in StringType. The
StringValue.parentID can be a row from any table that has a LONG
identity column. If a row in the parent table does not have a
corresponding row in StringValue, we want to assume an implicit default
value.

So to return all the Person rows that have either an explicit
StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I
can just execute the following query.

SELECT P.*
FROM Person P
 LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID
= 1
 LEFT JOIN StringType  ST1 ON ST1.ID = 1
 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID
= 2
 LEFT JOIN StringType  ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
  ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
  AND
  ( SV2.f_val LIKE 'bar' OR
  ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
  
The above seems to work fine. I can also get rid of the constant joins
on the type table and just do a subquery for the default value, either
approach works.

Now I want to change things to introduce a ValueLink table so that if
100 rows have the same string value that I don't have to write the
string 100 times. So my table structure changes like this:

TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue

TABLE StringValue
LONG ID PKEY  // the value unique ID
LONG typeID
VARCHAR val

TABLE StringLinks
LONG parentID PKEY
LONG valueID  PKEY

My naive approach to the query was this:

SELECT P.*
FROM Person P
 LEFT JOIN StringLinks L ON P.ID = L.parentID
 LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1
 LEFT JOIN StringType  ST1 ON ST1.ID = 1
 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2
 LEFT JOIN StringType  ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
  ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
  AND
  ( SV2.f_val LIKE 'bar' OR
  ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
  
As you can probably tell this wont work because the two values will now
show up in two different rows so the AND of the search terms will always
fail.

Can anyone think of a clean way to do this or do I have to get rid of
the joins altogether and do a subquery for each StringValue search term?

TIA,

R.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select as an array.

[Jo�o C�ndido de Souza Neto]

I found another solution myself.

Thanks.

""João Cândido de Souza Neto"" <[EMAIL PROTECTED]> escreveu na 
mensagem news:[EMAIL PROTECTED]
> Hi everyone,
>
> I´m not sure if it´s the right place to get such answer, but if someone 
> know, please, help me.
>
> In a select id,name,picture1,picture2,picture3 from product where id="10" 
> i get an array with each colum in each element like this $result ("id" => 
> "10", "name" => "name of product", "picture1" => "pic1.gif", "picture2" => 
> "pic2.gif", "picture3" => "pic3.gif").
>
> Is there any way in select to get something like this:
>
> $result ("id" => "10", "name" => "name of product", "pictures" => array( 
> "pic1" => "pic1.gif", "pic2" => "pic2.gif", "pic3" => "pic3.gif") ).
>
> -- 
> João Cândido de Souza Neto
> Curitiba Online
> [EMAIL PROTECTED]
> (41) 3324-2294 (41) 9985-6894
> http://www.curitibaonline.com.br
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Log files

[bruce]

hi...

i changed the mysql my.cnf to provide log files. i restarted mysql, but i
don't see the log files...

any idea as to what i may have missed. i'm running FC4, mysql 4.1.

the my.cnf is:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=1000
set-variable = log-error=/var/log/mysql_err.log
set-variable = log=/var/log/mysql_query.log
#set-variable = log-bin=/var/log/mysql_bin.log
set-variable = log-slow-queries=/var/log/mysql_slow.log
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


thanks



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Simultaneous connections

[bruce]

hi...

i'm creating an app that spawns child processes. each of these processes
will attempt to open a connection to the mysql db, and try to write/select
information to the db.

however, in doing initial testing, it appears that there is a limit to how
much/fast mysql can handle simultaneous connections. i've played with the
'max_connection' parameter, having set it to 1000 for now.

has anyone done any kind of playing/analysis in this area?

if there is a limit to the number of simultaneous connections that can be
handled by mysql, what are some of the options that have been implemented to
solve this kind of issue...

thanks

-bruce


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

select as an array.

[Jo�o C�ndido de Souza Neto]

Hi everyone,

I´m not sure if it´s the right place to get such answer, but if someone 
know, please, help me.

In a select id,name,picture1,picture2,picture3 from product where id="10" i 
get an array with each colum in each element like this $result ("id" => 
"10", "name" => "name of product", "picture1" => "pic1.gif", "picture2" => 
"pic2.gif", "picture3" => "pic3.gif").

Is there any way in select to get something like this:

$result ("id" => "10", "name" => "name of product", "pictures" => array( 
"pic1" => "pic1.gif", "pic2" => "pic2.gif", "pic3" => "pic3.gif") ).

-- 
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow queries

[Jon Molin]

On 8/17/06, Chris <[EMAIL PROTECTED]> wrote:


> Unfortunately didn't that help, it leads to:
> ++-+---+---+---
>
> | id | select_type | table | type  | possible_keys
>  | key   | key_len | ref | rows| Extra
>|
> ++-+---+---+---
>
> |  1 | SIMPLE  | ps| range |
> phrase_search,id_search,phrase_date | id_search | 3   | NULL
> | 3836930 | Using where; Using temporary; Using filesort

Yeh it's finding a lot more rows there which isn't what you want so the
extra time isn't surprising.


Does rewriting the query to be an inner join help?

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw INNER JOIN phrase_searches ps ON
(ps.phrase_id=pw.phrase_id) WHERE
pw.word_id IN (966,1,1250,1741) AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

or even:

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps WHERE
pw.phrase_id = ps.phrase_id AND pw.word_id IN (966,1,1250,1741) AND
ps.search_date >= '2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

(which puts the join between the two tables first).



That didn't help either. Same amount of rows as my first join and
about the same speed as well (only a few seconds differing when
executed).



That would help with this discussion too:
http://lists.mysql.com/mysql/201015

;)


Yes, it'd be sweet if that mysql internals guru revelead her/him-self
from the cloud of guruness and spoke the true way of doing it.

What pisses me off most is that 'grep -E "^word$| word$|^word | word "
2006/07/*/phrases |wc -l' is so much quicker than the db :(

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stability of MySQL 5.1.11

[Joerg Bruehe]

Hi Jacques, all!


Jacques Marneweck wrote:

Hi,

I'm wondering what the status of MySQL 5.1.11 is regarding the roadmap 
for 5.1 changing from "beta" to "generally available"?


5.1.11 is labeled "beta", like all 5.1 versions since 5.1.7.

When more issues have been sorted out, the status will change to "rc" 
("release candidate", formerly called "gamma"), so there will be some 
releases "5.1.x-rc" .. "5.1.y-rc". Currently, nobody can predict the 
values of "x" and "y" or their release dates.


After that, 5.1.z will be "GA" ("generally available"). Again, nobody 
can predict the value of "z" or the date.

As it was put once: "It will be ready when it is ready".

The only speedup possible is by heavy use of the "beta" versions, and 
then reporting bugs or (more hopefully) successes.



Regards,
Joerg

--
Joerg Bruehe, Senior Production Engineer
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: Slow queries

[Chris]

Unfortunately didn't that help, it leads to:
++-+---+---+---

| id | select_type | table | type  | possible_keys
 | key   | key_len | ref | rows| Extra
   |
++-+---+---+---

|  1 | SIMPLE  | ps| range |
phrase_search,id_search,phrase_date | id_search | 3   | NULL
| 3836930 | Using where; Using temporary; Using filesort


Yeh it's finding a lot more rows there which isn't what you want so the 
extra time isn't surprising.



Does rewriting the query to be an inner join help?

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw INNER JOIN phrase_searches ps ON 
(ps.phrase_id=pw.phrase_id) WHERE

pw.word_id IN (966,1,1250,1741) AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

or even:

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps WHERE
pw.phrase_id = ps.phrase_id AND pw.word_id IN (966,1,1250,1741) AND
ps.search_date >= '2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

(which puts the join between the two tables first).


That would help with this discussion too: 
http://lists.mysql.com/mysql/201015


;)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow queries

[Jon Molin]

On 8/17/06, Chris <[EMAIL PROTECTED]> wrote:

Jon Molin wrote:
> Hi list
>
> I have 5 tables:
>
> words (word_id int auto_increment, word varbinary(40)) (has ~3.5M
> rows) with the keys:
> PRIMARY KEY  (`word_id`),UNIQUE KEY `word_ind` (`word`)
>
> phrases (phrase_id int auto_increment, phrase varbinary(100)) (has
> ~11M rows) with the keys:
> PRIMARY KEY  (`phrase_id`),UNIQUE KEY `phrase_ind` (`phrase`)
>
> phrase_words (phrase_id, word_id) (has ~31M rows) with:
> UNIQUE KEY `phrase_ind` (`phrase_id`,`word_id`)
> KEY `word` (`word_id`), KEY `phrase` (`phrase_id`)
>
> word_searches (word_id, search_date date, search hour char(2), amount
> smallint, type char(8), location char(2)) with:
> KEY `word_search` (`word_id`),
> KEY `id_search` (`search_date`),
> KEY `word_date` (`word_id`,`search_date`)
>
> (and a similar for phrase_searches, these two tables are merge tables
> with one table for each month, each table having 15-30M rows)
>
> phrases are built of  "words" identified by phrase_words (these are
> not human language words and phrases but rather random bytes where
> some are human readable).
>
> Now, I'm trying to find out "how many times has word 1..n been
> searched for and how many times has phrases containing 1..n been
> searched for?"
>
> These queries take a really long time to execute, first I select for the
> words:
> explain sELECT w.word as word, w.word_id as word_id, sum(ws.amount) as
> amount FROM words w, word_searches ws WHERE
> ws.word_id=w.word_id AND w.word IN ("p", "xyz", "zzz", "abc") AND
>  ws.search_date >= '2006-07-17' AND ws.search_date <=
> '2006-08-16' group by ws.word_id;
> 
++-+---+---+-+-+-+--+--+--+
>
> | id | select_type | table | type  | possible_keys   |
> key | key_len | ref  | rows | Extra
>|
> 
++-+---+---+-+-+-+--+--+--+
>
> |  1 | SIMPLE  | w | range | PRIMARY,word_ind|
> word_ind| 42  | NULL |4 | Using where;
> Using temporary; Using filesort |
> |  1 | SIMPLE  | ws| ref   | word_search,id_search,word_date |
> word_search | 4   | statistics.w.word_id |   15 | Using where
>|
> 
++-+---+---+-+-+-+--+--+--+
>
>
> and then for phrases:
> explain SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
> phrase_words pw, phrase_searches ps WHERE
>pw.word_id IN (966,1,1250,1741) AND
>pw.phrase_id = ps.phrase_id AND ps.search_date >=
> '2006-07-17' AND ps.search_date <= '2006-08-16'
>GROUP by pw.word_id;
> 
++-+---+---+-+---+-+-++-+
>
> | id | select_type | table | type  | possible_keys
>  | key   | key_len | ref | rows   | Extra
>  |
> 
++-+---+---+-+---+-+-++-+
>
> |  1 | SIMPLE  | pw| range | phrase_ind,word,phrase
>  | word  | 4   | NULL| 226847 | Using
> where |
> |  1 | SIMPLE  | ps| ref   |
> phrase_search,id_search,phrase_date | phrase_search | 4   |
> statistics.pw.phrase_id | 15 | Using where |
> 
++-+---+---+-+---+-+-++-+

The problem is it's picking the "word" index which apparently is
returning 226,000+ areas.

Test this:

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps IGNORE INDEX (word) WHERE
pw.word_id IN (966,1,1250,1741) AND
pw.phrase_id = ps.phrase_id AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

(that should ignore the 'word' index and instead use the 'phrase' index).



Unfortunately didn't that help, it leads to:
++-+---+---+-+---+-+-+-+--+
| id | select_type | table | type  | possible_keys
 | key   | key_len | ref | rows| Extra
   |
++-+---+---+-+---+-+-+-+--+
|  1 | SIMPLE  | ps| ran

Re: mysqladmin claims password in crontab !

[Chris]

Marc MENDEZ wrote:

Hi,

For mysqladmin, this parameter does not exist. I'll try anyway by 
forcing password and user on the command line.

I'll check tomorrow.


Sure it does.

$ mysqladmin --help | grep extra
--defaults-extra-file=# Read this file after the global files are read


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow queries

[Chris]

Jon Molin wrote:

Hi list

I have 5 tables:

words (word_id int auto_increment, word varbinary(40)) (has ~3.5M
rows) with the keys:
PRIMARY KEY  (`word_id`),UNIQUE KEY `word_ind` (`word`)

phrases (phrase_id int auto_increment, phrase varbinary(100)) (has
~11M rows) with the keys:
PRIMARY KEY  (`phrase_id`),UNIQUE KEY `phrase_ind` (`phrase`)

phrase_words (phrase_id, word_id) (has ~31M rows) with:
UNIQUE KEY `phrase_ind` (`phrase_id`,`word_id`)
KEY `word` (`word_id`), KEY `phrase` (`phrase_id`)

word_searches (word_id, search_date date, search hour char(2), amount
smallint, type char(8), location char(2)) with:
KEY `word_search` (`word_id`),
KEY `id_search` (`search_date`),
KEY `word_date` (`word_id`,`search_date`)

(and a similar for phrase_searches, these two tables are merge tables
with one table for each month, each table having 15-30M rows)

phrases are built of  "words" identified by phrase_words (these are
not human language words and phrases but rather random bytes where
some are human readable).

Now, I'm trying to find out "how many times has word 1..n been
searched for and how many times has phrases containing 1..n been
searched for?"

These queries take a really long time to execute, first I select for the 
words:

explain sELECT w.word as word, w.word_id as word_id, sum(ws.amount) as
amount FROM words w, word_searches ws WHERE
ws.word_id=w.word_id AND w.word IN ("p", "xyz", "zzz", "abc") AND
 ws.search_date >= '2006-07-17' AND ws.search_date <=
'2006-08-16' group by ws.word_id;
++-+---+---+-+-+-+--+--+--+ 


| id | select_type | table | type  | possible_keys   |
key | key_len | ref  | rows | Extra
   |
++-+---+---+-+-+-+--+--+--+ 


|  1 | SIMPLE  | w | range | PRIMARY,word_ind|
word_ind| 42  | NULL |4 | Using where;
Using temporary; Using filesort |
|  1 | SIMPLE  | ws| ref   | word_search,id_search,word_date |
word_search | 4   | statistics.w.word_id |   15 | Using where
   |
++-+---+---+-+-+-+--+--+--+ 



and then for phrases:
explain SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps WHERE
   pw.word_id IN (966,1,1250,1741) AND
   pw.phrase_id = ps.phrase_id AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
   GROUP by pw.word_id;
++-+---+---+-+---+-+-++-+ 


| id | select_type | table | type  | possible_keys
 | key   | key_len | ref | rows   | Extra
 |
++-+---+---+-+---+-+-++-+ 


|  1 | SIMPLE  | pw| range | phrase_ind,word,phrase
 | word  | 4   | NULL| 226847 | Using
where |
|  1 | SIMPLE  | ps| ref   |
phrase_search,id_search,phrase_date | phrase_search | 4   |
statistics.pw.phrase_id | 15 | Using where |
++-+---+---+-+---+-+-++-+ 


The problem is it's picking the "word" index which apparently is 
returning 226,000+ areas.


Test this:

EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps IGNORE INDEX (word) WHERE
pw.word_id IN (966,1,1250,1741) AND
pw.phrase_id = ps.phrase_id AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;

(that should ignore the 'word' index and instead use the 'phrase' index).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slow queries

[Jon Molin]

Hi list

I have 5 tables:

words (word_id int auto_increment, word varbinary(40)) (has ~3.5M
rows) with the keys:
PRIMARY KEY  (`word_id`),UNIQUE KEY `word_ind` (`word`)

phrases (phrase_id int auto_increment, phrase varbinary(100)) (has
~11M rows) with the keys:
PRIMARY KEY  (`phrase_id`),UNIQUE KEY `phrase_ind` (`phrase`)

phrase_words (phrase_id, word_id) (has ~31M rows) with:
UNIQUE KEY `phrase_ind` (`phrase_id`,`word_id`)
KEY `word` (`word_id`), KEY `phrase` (`phrase_id`)

word_searches (word_id, search_date date, search hour char(2), amount
smallint, type char(8), location char(2)) with:
KEY `word_search` (`word_id`),
KEY `id_search` (`search_date`),
KEY `word_date` (`word_id`,`search_date`)

(and a similar for phrase_searches, these two tables are merge tables
with one table for each month, each table having 15-30M rows)

phrases are built of  "words" identified by phrase_words (these are
not human language words and phrases but rather random bytes where
some are human readable).

Now, I'm trying to find out "how many times has word 1..n been
searched for and how many times has phrases containing 1..n been
searched for?"

These queries take a really long time to execute, first I select for the words:
explain sELECT w.word as word, w.word_id as word_id, sum(ws.amount) as
amount FROM words w, word_searches ws WHERE
ws.word_id=w.word_id AND w.word IN ("p", "xyz", "zzz", "abc") AND
 ws.search_date >= '2006-07-17' AND ws.search_date <=
'2006-08-16' group by ws.word_id;
++-+---+---+-+-+-+--+--+--+
| id | select_type | table | type  | possible_keys   |
key | key_len | ref  | rows | Extra
   |
++-+---+---+-+-+-+--+--+--+
|  1 | SIMPLE  | w | range | PRIMARY,word_ind|
word_ind| 42  | NULL |4 | Using where;
Using temporary; Using filesort |
|  1 | SIMPLE  | ws| ref   | word_search,id_search,word_date |
word_search | 4   | statistics.w.word_id |   15 | Using where
   |
++-+---+---+-+-+-+--+--+--+

and then for phrases:
explain SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps WHERE
   pw.word_id IN (966,1,1250,1741) AND
   pw.phrase_id = ps.phrase_id AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
   GROUP by pw.word_id;
++-+---+---+-+---+-+-++-+
| id | select_type | table | type  | possible_keys
 | key   | key_len | ref | rows   | Extra
 |
++-+---+---+-+---+-+-++-+
|  1 | SIMPLE  | pw| range | phrase_ind,word,phrase
 | word  | 4   | NULL| 226847 | Using
where |
|  1 | SIMPLE  | ps| ref   |
phrase_search,id_search,phrase_date | phrase_search | 4   |
statistics.pw.phrase_id | 15 | Using where |
++-+---+---+-+---+-+-++-+

The queries takes 40s-several minutes on a dual xeon 3GHz with 4GB ram
only running Msql.

Can someone see something I've done wrong? I have the same data in
flat files with one word and phrase on each row and one file for each
day and doing grep/sort/uniq -c in all thoose files is quicker on a
slower server with a lot of other procesess and with the files nfs
mounted.

mysqladmin status doesn't show any slow queries:
Uptime: 1215323  Threads: 2  Questions: 2191970  Slow queries: 0
Opens: 0  Flush tables: 1  Open tables: 64  Queries per second avg:
1.804

Thanks in advance
/Jon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]