mysql can't start up

Hi
  I have installed mysql some software on aix5.2 .
the edition is mysql-standard-4.0.21-ibm-aix5.2.0.0-powerpc
but mysql  can't run
when I used the command safe_mysqld start the system told me 
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/localhost.pid
040920 14:34:38  mysqld ended

Many friends tell me to see the err log, the below is the err log(locaohost.err)
**
040920 14:25:48  mysqld started
exec(): 0509-036 Cannot load program /usr/local/mysql/bin/mysqld because of the 
following errors:
0509-130 Symbol resolution failed for mysqld because:
0509-136   Symbol mkstemp64 (number 130) is not exported from
   dependent module /usr/lib/libc.a(shr.o).
0509-192 Examine .loader section symbols with the
 'dump -Tv' command.
040920 14:25:48  mysqld ended

***


help me please ,how to resolve

thanks a lot
Yours  
   LiRui
[EMAIL PROTECTED]
2004-09-20

displaying MySQL query results differently

[gowthaman ramasamy]

Dear list,

I want to display the result of the some mysql query differently .. 
I have a mysql table(allreps_orgs) with 2500,000 entries.

QUERY IS:
select distinct * from 
allreps_orgs as p1,allreps_orgs as p2, 
where p1.organism='Plasmodium' AND p2.organism='Caenorhabditis'  
AND p1.repeat=p2.repeat;
--
It displays the result correctly. But it joins two entries having same
values in repeat coloum. 

the typical result row looks like this.
|16804919|LKEKE|5|2|Plasmodium|1|155|22329777|LKEKE|5|2|Caenorhabditis|4|234|

In which first 7 columns are coming from one row and next 7 are from
another row which are satisfying the condition. 

I want these to be displayed in two different rows (first 7 in row 1 and
second 7 in row 2). Is it possible to that.

I could do it by displaying p1.* and p2.* in two different quries. But
that doubles the querying time. Any other suggestions or pointers.

following is my table structure. 
Note: i compare blob filed which is indexed in first 10 char. 
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| prot_id  | varchar(15)  | YES  | MUL | NULL|   |
| repeat   | blob | YES  | MUL | NULL|   |
| size_rep | int(10) unsigned | YES  | | NULL|   |
| freq | varchar(5)   | YES  | | NULL|   |
| organism | varchar(100) | YES  | | NULL|   |
| version  | char(3)  | YES  | | NULL|   |
| cordis   | blob | YES  | | NULL|   |




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

Re: database relations

[SGreen]

If by relations you mean foreign keys the answer is YES. Use the 
InnoDB table type and read these instructions:

http://dev.mysql.com/doc/mysql/en/ANSI_diff_Foreign_Keys.html
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

Don't forget to follow the hyperlinks for additional details!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

tommie ramirez andujar [EMAIL PROTECTED] wrote on 09/17/2004 
06:05:49 AM:

 Hello folks
 
 Can I use or implement database relations in sql as some do in databases 

 in accecss..?
 
 tommie
 -- 
 ___
 msc. tomas alberto ramirez.andujar
 webmaster - cejisoft - +5332 262451 /261410
 universidad pedagogica jose marti. camaguey
 circunvalacion norte km 51/2 cp 74670. cuba
 [EMAIL PROTECTED] - www.esicm.cu/cejisoft
 
 
 Este mensaje ha sido analizado por MDaemon Antivirus v2.21.
 Instituto Superior Pedagógico de Camaguey, Cuba.
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: mysql installation

[SGreen]

I recommend starting with the online manual. It has a section on 
installation and a short tutorial section to help you get used to the 
MySQL client.

http://dev.mysql.com/doc/mysql/en/index.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Caulfield, Linda [EMAIL PROTECTED] wrote on 09/17/2004 12:41:54 
PM:

 Hello,
 
 I am trying to setup mysql and need some guidance -
 
 thanks
 
 Linda Caulfield
 Software Quality Engineer 
 Global Hawk Program 
 Rancho Bernardo X4680
 Phone:858.618.4680
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: OUTER JOIN bug in 5.0.1?

[SGreen]

Sounds like your 4.0.20 may be the buggy installation... let me see if I 
can explain.

Let's analyze your FROM clause and imagine there is no WHERE clause, for 
the moment:

FROM student s
INNER JOIN enrollment e ON e.tech_id = s.tech_id
INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
RIGHT JOIN assignment a ON a.a_id = sa.a_id

The tables student, enrollment, and submitted_assignment are all INNER 
JOINed to each other. You won't get any records back from those tables 
UNLESS they satisfy 
the ON statements that match records between them respectively.  That 
batch of records is RIGHT JOINed to assignment. That means that all of the 
records from assignment are returned and the other 3 tables just became 
optional data. Their data will be added to the internal working table 
only if they meet the ON condition of the RIGHT JOIN.

Here is a logically equivalent way of reformatting your original FROM 
clause

FROM assignment a 
LEFT JOIN submitted_assignment sa
ON a.a_id = sa.a_id
INNER JOIN student s
on sa.tech_id = s.tech_id
INNER JOIN enrollment e
on e.tech_ID = sa.tech_ID

With it arranged this way, can you see why you should get only those 
students that completed their assignments?

Changing your original query to use a LEFT JOIN in place of the RIGHT JOIN 
will probably solve your problem.


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Josh Trutwin [EMAIL PROTECTED] wrote on 09/17/2004 04:14:57 PM:

 Is there a known bug with outer joins in MySQL 5.0.1?
 
 I tried the following query on 5.0.1:
 
 SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
 FROM student s
 INNER JOIN enrollment e ON e.tech_id = s.tech_id
 INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
 RIGHT JOIN assignment a ON a.a_id = sa.a_id 
 WHERE a.a_id = '100' AND e.term_id = '3'
 ORDER BY s.full_name;
 
 None of the outer results are present.  At first I thought the query
 was wrong, but if I run the same exact query using the same tables
 (from a mysqldump) on a 4.0.20 server I get the expected results
 including student's who have not yet submitted the assignment.
 
 Running on SuSE linux 5.0.1 compiled from source.
 
 Any more details I can provide?  I have to imagine that something like
 this has already been found.  Is the 5.0.1 snapshot on the products
 download page updated frequently?  If so I guess I could try to
 download and install again.
 
 Josh
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: OUTER JOIN bug in 5.0.1?

[Josh Trutwin]

On Mon, 20 Sep 2004 09:33:56 -0400
[EMAIL PROTECTED] wrote:

 Sounds like your 4.0.20 may be the buggy installation... let me see
 if I can explain.

Except this is a 5.0.1 installation.  The query worked as is in 4.0.20
(and it also worked in 5.0.0), only after playing with 5.0.1 did the
results suddenly change.

 Let's analyze your FROM clause and imagine there is no WHERE clause,
 for the moment:
 
 FROM student s
 INNER JOIN enrollment e ON e.tech_id = s.tech_id
 INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
 RIGHT JOIN assignment a ON a.a_id = sa.a_id
 
 The tables student, enrollment, and submitted_assignment are all
 INNER JOINed to each other. You won't get any records back from
 those tables UNLESS they satisfy 
 the ON statements that match records between them respectively. 
 That batch of records is RIGHT JOINed to assignment. That means that
 all of the records from assignment are returned and the other 3
 tables just became optional data. Their data will be added to the
 internal working table only if they meet the ON condition of the
 RIGHT JOIN.
 
 Here is a logically equivalent way of reformatting your original
 FROM clause
 
 FROM assignment a 
 LEFT JOIN submitted_assignment sa
 ON a.a_id = sa.a_id
 INNER JOIN student s
 on sa.tech_id = s.tech_id
 INNER JOIN enrollment e
 on e.tech_ID = sa.tech_ID

Yes, I tried re-arranging things like this, and as it is above I think
it's more readable, but I was unable to get any results that resembled
an outer join.  Unfortunately I cannot test this out at the moment due
to other issues.

Thanks for your help though,

Josh

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

Re: auto increment fields

[SGreen]

By replacing deleted records with new information, you run the risk of 
misidentifying data in related tables. What if you had a record in a table 
called person with an ID of 6 that belonged to Mary Jones. You delete it 
and create a new record 6 for Bob Mondo?  Let's say you have a related 
table that contains phone numbers AND that you somehow neglected to erase 
the records from phnumbers where the person_id was 6 when you deleted 
Mary's record from the person table. Now that you have created Bob's 
record, those phone numbers seem to belong to him. Based on your data how 
could you tell they weren't Bob's phone numbers? Can you see where this 
can become a complicted situation if your database were more complex?

Gaps in your sequence numbers are a normal consequence of having an active 
database. You can expect a sequentially assigned number to relate to only 
1 record per table. When that record goes away, it's sequence number goes 
away too. 

IF you MUST recycle your sequence numbers for some reason, add a field to 
your table so that you can flag a record as deleted. Then when it comes 
time to insert new records you will have to UPDATE the flagged records 
with the new information and reset the flag to active. For any data 
beyond that which will fit into the available deleted records, you will 
have to revert to a standard INSERT statement.  IMHO, that is way too much 
effort to spend on keeping your sequentially assigned numbers in 
sequential order and without gaps. Your database performance will be 
severely degraded by all of the extra management you have to perform each 
time to need to add a record to your database.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Mike [EMAIL PROTECTED] wrote on 09/19/2004 04:59:02 PM:

 hi.
 I have a rather childish question on tables and auto increment fields.
 Scenario: I have a table with an int auto increment primary key field. 
The
 deal is that everything works fine (I'm talking about the auto 
 incrementation part)
 until I choose to delete a row. This creates a gap in the primary key 
field.
 And my question is: I'd like to know if there is an easier way to keep 
track
 of these gaps, instead of specifically iterating through the table and
 stopping where you find one. To accomplish this, I use this function:
 
 function GetUntakenNrCrt($tabel) {
  $nrCrt = 1;
  while(10) {
   if(!GetSingle(select nrcrt from $tabel where nrcrt='$nrCrt')) return 

 $nrCrt;
   $nrCrt++;
  }
 }
 
 function GetSingle($query) {
  $q = mysql_query($query);
  if(mysql_num_rows($q)  0)
   while($p = mysql_fetch_row($q)) return $p[0];
  else return false;
 }
 
 The reason is that I want a table with continous records in the primary 
key
 field (1, 2, 3... instead of 1,6,23...).
 Can anyone suggest a different (and easier) method? Thanx a lot! 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: OUTER JOIN bug in 5.0.1?

[SGreen]

I think you missed my point. I think the 5.0.1 behavior was correct and 
the others are wrong. There is a known bug (or two) about mixing outer 
joins and inner joins and it looks like it may be fixed. IF you want to 
see all of the students THAT TABLE (students) needs to be on the LEFT side 
of a LEFT JOIN or the RIGHT side of a RIGHT JOIN. That's what the 
directions mean

SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e 
ON e.tech_id = s.tech_id
AND e.term_id = '3'
LEFT JOIN submitted_assignment sa 
ON sa.tech_id = s.tech_id 
LEFT JOIN assignment a 
ON a.a_id = sa.a_id 
AND a.a_id = '100'
ORDER BY s.full_name;

I also moved the clause AND a.a_id = '100' into the ON portion of the 
LEFT JOIN. That way you can see who did and didn't get that assignment.

If you describe what you WANT to see, I can help you write the query to 
get it. What I think I wrote for you will be all students where term_ID=3 
and what grades they got on assignment 100. But i think you may get some 
duplicate rows of blank scores. Does assignment relate to student, 
perhaps with a tech_id or student_id field? That fixes one dilemma by 
setting up the following query

SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as 
assigned_100, sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e 
ON e.tech_id = s.tech_id
AND e.term_id = '3'
LEFT JOIN assignment a 
ON a.student_ID = s.student_ID
AND a.a_id = '100'
LEFT JOIN submitted_assignment sa 
ON sa.tech_id = s.tech_id 
AND a.a_id = sa.a_id 
ORDER BY s.full_name;

It would also help me if you basically explained what each table 
represents and how they relate to each other.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Josh Trutwin [EMAIL PROTECTED] wrote on 09/20/2004 09:50:58 AM:

 On Mon, 20 Sep 2004 09:33:56 -0400
 [EMAIL PROTECTED] wrote:
 
  Sounds like your 4.0.20 may be the buggy installation... let me see
  if I can explain.
 
 Except this is a 5.0.1 installation.  The query worked as is in 4.0.20
 (and it also worked in 5.0.0), only after playing with 5.0.1 did the
 results suddenly change.
 
  Let's analyze your FROM clause and imagine there is no WHERE clause,
  for the moment:
  
  FROM student s
  INNER JOIN enrollment e ON e.tech_id = s.tech_id
  INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
  RIGHT JOIN assignment a ON a.a_id = sa.a_id
  
  The tables student, enrollment, and submitted_assignment are all
  INNER JOINed to each other. You won't get any records back from
  those tables UNLESS they satisfy 
  the ON statements that match records between them respectively. 
  That batch of records is RIGHT JOINed to assignment. That means that
  all of the records from assignment are returned and the other 3
  tables just became optional data. Their data will be added to the
  internal working table only if they meet the ON condition of the
  RIGHT JOIN.
  
  Here is a logically equivalent way of reformatting your original
  FROM clause
  
  FROM assignment a 
  LEFT JOIN submitted_assignment sa
  ON a.a_id = sa.a_id
  INNER JOIN student s
  on sa.tech_id = s.tech_id
  INNER JOIN enrollment e
  on e.tech_ID = sa.tech_ID
 
 Yes, I tried re-arranging things like this, and as it is above I think
 it's more readable, but I was unable to get any results that resembled
 an outer join.  Unfortunately I cannot test this out at the moment due
 to other issues.
 
 Thanks for your help though,
 
 Josh
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Eventual connection looses

[Luis Mediero]

Hi,

I have a web application with servlets and jsp's. I use a connection pool to
optimize connections to database.
Usualy all works fine but sometimes, after a period of inactivity (30-50
minutes), connections are lossed. This only occurs if mysql and web server
are in different machines.
In MySql Control Center I can see connections but my application seems not
to see them.

Every time the application needs a connection, the system ask for one to the
pool.
The pool searchs for a free connection and checks it with a kind of ping
select curdate() before assigning it to the application. This prevents the
use of timeouted connections.
The problem is that sometimes, this ping doesnt't responde for a long time,
until 13'.
I've tried to adjust the connectTimeout of jdbc driver but nothing changes.
If I adjust the jdbd driver parameter socketTimeout to 2 seconds, after 2
seconds the ping returns fail and I try to create a new connection with
success.
This is working fine but this parameter affects to all operations against
db, that's to say, if the user ask for a long query, after 2 seconds the
system cuts the connection.

Any ideas to prevent this looses without cutting long queries?

Thanks in advance

Begoña Villamor


Environment: Apache 2.0.46, Tomcat 4.1.30, mod_jk 1.2.5, MySql 4.0.20, mysql
jdbc driver 3.0.7 , jdk 1.4.2_05
Driver properties are:
autoReconnect: false  (No difference with this property true)
maxReconnects: 3, initialTimeout: 2
socketTimeout: 2000, interactiveClient: false
useCompression: false,  connectTimeout: 0 (No difference with this property
2000)
Server variables:
'connect_timeout','5', 'delayed_insert_limit','100',
'delayed_insert_timeout','300'
'delayed_queue_size','1000', 'interactive_timeout','28800',
'long_query_time','10'
'max_connect_errors','10', 'max_connections','100',
'max_delayed_threads','20'
'max_insert_delayed_threads','20', 'max_user_connections','0',
'net_read_timeout','30'
'net_retry_count','10', 'net_write_timeout','60', 'skip_networking','OFF'
'slave_net_timeout','3600', 'wait_timeout','28800'


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

Re: Query takes terribly long

[Bill Easton]

Dirk,

If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.

Here's the query you asked about.

SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
  AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
  AND YEAR(putdatetime)  YEAR('2004-09-19')
  AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30

I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.

It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:

  putdatetime  '2003-09-20'

MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.

You can use

  putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)

and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.

Of course, if you really want just the data for the same day last year, you
could use

SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
  AND put=1 AND front=1
  AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30

HTH, Bill

== Dirk Schippers wrote: ==

Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long

No,

I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!

Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.

Thanks for all your help!
Dirk.

Jocelyn Fournier wrote:

Hi,

The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?

Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long




Hello,

Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that didn't change things, it might have sped
up the other queries on the table.

Probably this query can't be sped up because of the functions I use.

Is there another thing I can try to speed the query up?

And another question, is it normal that even when not using the cache
(always executing RESET QUERY CACHE; before the query), the query
sometimes only takes 0.33 seconds, and at other times the query takes 22
seconds? Is this something everyone here experiences? Or is there
something wrong with my configuration?

Regards,
Dirk.

Jocelyn Fournier wrote:



Hi,

I would say for this one you need an index on (put, front, putdatetime)


to


get rid of the filesorting.
Note that because you're transforming the putdatetime column with


functions,


MySQL will not be able to use index infos of putdatetime.
So it will use put and front index information to find the result, and
putdatetime to order the result.
If you add topcategory in your query, you will see MySQL will use the


index


(put, front, topcategory, putdatetime) without filesorting.

Regards,
 Jocelyn Fournier
 www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 11:55 AM
Subject: Re: Query takes terribly long






-- 

Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com

Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.


--060102010907050706010607--


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

Re: Query takes terribly long

[Dirk Schippers]

Wow Bill,
Thanks!
It's so obvious! But only experience can bring such sollutions.
I'll change the query to a more hardcoded-look!
Thanks again!
Dirk.
Bill Easton wrote:
Dirk,
If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.
Here's the query you asked about.
SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
 AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
 AND YEAR(putdatetime)  YEAR('2004-09-19')
 AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30
I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.
It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:
 putdatetime  '2003-09-20'
MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.
You can use
 putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)
and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.
Of course, if you really want just the data for the same day last year, you
could use
SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
 AND put=1 AND front=1
 AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30
HTH, Bill
== Dirk Schippers wrote: ==
Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long
No,
I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!
Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.
Thanks for all your help!
Dirk.
Jocelyn Fournier wrote:
 

Hi,
The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long


   

Hello,
Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that didn't change things, it might have sped
up the other queries on the table.
Probably this query can't be sped up because of the functions I use.
Is there another thing I can try to speed the query up?
And another question, is it normal that even when not using the cache
(always executing RESET QUERY CACHE; before the query), the query
sometimes only takes 0.33 seconds, and at other times the query takes 22
seconds? Is this something everyone here experiences? Or is there
something wrong with my configuration?
Regards,
Dirk.
Jocelyn Fournier wrote:

 

Hi,
I would say for this one you need an index on (put, front, putdatetime)
   

to
   

get rid of the filesorting.
Note that because you're transforming the putdatetime column with
   

functions,
   

MySQL will not be able to use index infos of putdatetime.
So it will use put and front index information to find the result, and
putdatetime to order the result.
If you add topcategory in your query, you will see MySQL will use the
   

index
   

(put, front, topcategory, putdatetime) without filesorting.
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 11:55 AM
Subject: Re: Query takes terribly long



   

 

--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com

Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.

intermediate table question

[Giulio]

I have two tables, let's say AudioTrack and Category
Every AudioTrack record can belong to one or more ( or none ) 
Categories.
I have created an intermediate table, AudioTracks_ Categories 
containing only the IDs of AudioTrack and Category to keep track of the 
link.

to find all tracks belonging to a given list of categories, the query I 
use is ( this was another tip from this list ):

   SELECT AudioTrack.*
   FROM AudioTrack JOIN AudioTracks_Categories
   ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
   WHERE AudioTracks_Categories.Category_id IN (cat1,cat2,...)
   GROUP BY AudioTrack.AudioTrack_id
   HAVING COUNT(*) = number_of_categories_to_match
my problem now is, how can i find the data tracks belonging to a given 
list of categories, and retrieve on the same query ALL the categories 
they belong to?

I mean, using:
   SELECT AudioTrack.*
   FROM AudioTrack JOIN AudioTracks_Categories
   ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
   WHERE AudioTracks_Categories.Category_id IN (1,3,7)
   GROUP BY AudioTrack.AudioTrack_id
   HAVING COUNT(*) = 3
I know that all the tracks retrieved on the query belong to categories 
1,3 and 7, but some of them could belong to more categories not 
specified in the query, and I would like to have also them in the 
result...

Hope this was clear...
thank you
  Giulio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update query return value

[Jeff Demel]

I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something the 
MySQLDirect .NET provider is doing, and to cover that possibility I've 
sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to refresh 
it, and am getting 0 as a return value. If I send different data, then I 
get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,thisname,thisdescription
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but really 
think I should be getting a 1.  I would think that if there was no ID 
with a value of 5, then it would return 0.  But if there is a row with 
an ID of 5, then it should refresh the row and return 1.  Right?

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

DBD::MySQL

[Nathan Mealey]

Has anyone out there had any trouble installing DBD::MySQL for MySQL 
4.1.x on Mac OS X 10.3?  I have tried it on several machines in my 
company's office, but it fails on all that are running 4.1.x, and 
installs fine on those running 4.0.x

Thoughts?
Thanks,
Nathan
--
Nathan Mealey
Director of Operations
Cycle-Smart, Inc.
P.O. Box 1482
Northampton, MA
01061-1482
[EMAIL PROTECTED]
(413) 587-3133
(413) 210-7984 Mobile
(512) 681-7043 Fax
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Brent Baisley]

No, MySQL will indicate if anything in the row has changed. If you are 
updating with the same data, than nothing changes and MySQL doesn't 
waste the time to lock the table, write the data and update the 
indexes. It's much more efficient this way.

On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something the 
MySQLDirect .NET provider is doing, and to cover that possibility I've 
sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to refresh 
it, and am getting 0 as a return value. If I send different data, then 
I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,thisname,thisdescription
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but really 
think I should be getting a 1.  I would think that if there was no ID 
with a value of 5, then it would return 0.  But if there is a row with 
an ID of 5, then it should refresh the row and return 1.  Right?

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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

libmysqlclient.so.10 is needed

[Tim Johnson]

Hello:
Installing MySQL-server-4.0.21-0.i386.rpm
on Red Hat 9.0

Getting the following:
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) MySQL-python-0.9.1-6
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3
libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2

I presume that I need other packages. What packages would that be?
thanks
tim
-- 
Tim Johnson [EMAIL PROTECTED]
  http://www.alaska-internet-solutions.com

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

Re: intermediate table question

[SGreen]

I would first store your original query's results in a temp table:

CREATE TEMPORARY TABLE tmpTracks
SELECT AudioTrack.*
FROM AudioTrack 
INNER JOIN AudioTracks_Categories
ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
WHERE AudioTracks_Categories.Category_id IN (cat1,cat2,...)
GROUP BY AudioTrack.AudioTrack_id
HAVING COUNT(*) = number_of_categories_to_match

Then join to the temp table to build the full list of categories to get 
all of the categories for those tracks that were in all of the specific 
categories you declared in the first query.

SELECT tt.*, atc.Category_Id
FROM tmpTracks tt
INNER JOIN AudioTracks_Categories atc
ON atc.AudioTrack_ID = tt.AudioTrack_Id;

DROP TABLE tmpTracks;

The only other option would be to use a sub-select/sub-query which you may 
or may not be able to execute.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Giulio [EMAIL PROTECTED] wrote on 09/20/2004 03:13:28 PM:

 I have two tables, let's say AudioTrack and Category
 Every AudioTrack record can belong to one or more ( or none ) 
 Categories.
 I have created an intermediate table, AudioTracks_ Categories 
 containing only the IDs of AudioTrack and Category to keep track of the 
 link.
 
 to find all tracks belonging to a given list of categories, the query I 
 use is ( this was another tip from this list ):
 
 SELECT AudioTrack.*
 FROM AudioTrack JOIN AudioTracks_Categories
 ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
 WHERE AudioTracks_Categories.Category_id IN (cat1,cat2,...)
 GROUP BY AudioTrack.AudioTrack_id
 HAVING COUNT(*) = number_of_categories_to_match
 
 my problem now is, how can i find the data tracks belonging to a given 
 list of categories, and retrieve on the same query ALL the categories 
 they belong to?
 
 I mean, using:
 
 SELECT AudioTrack.*
 FROM AudioTrack JOIN AudioTracks_Categories
 ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
 WHERE AudioTracks_Categories.Category_id IN (1,3,7)
 GROUP BY AudioTrack.AudioTrack_id
 HAVING COUNT(*) = 3
 
 I know that all the tracks retrieved on the query belong to categories 
 1,3 and 7, but some of them could belong to more categories not 
 specified in the query, and I would like to have also them in the 
 result...
 
 Hope this was clear...
 
 thank you
 
Giulio
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: update query return value

[Brent Baisley]

I'm not following why you need to force an update? You mentioned a row 
refresh, but I'm not sure in what context. If you are looking to find 
out if a row has changed since you last read it, then you should have a 
timestamp field. The first timestamp field is always updated when data 
changes in a record, so you could use this as sort of a record 
versioning system. Just query the timestamp field to check if the data 
has changed, if it has, then do the full query to retrieve the entire 
record.

Even if you create a field that you change on every update, MySQL only 
changes the data in fields that have changed, not in all the fields in 
your update statement. Paul DuBois she be able to correct if I'm wrong 
on this. I can't think of any reason to force a rewrite the same data 
to disk.

On Sep 20, 2004, at 3:49 PM, Jeff Demel wrote:
That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending 
(pull the record, compare the data, then decide to run the update or 
not). How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as an 
Int, and update that every time (TimesUpdated = TimesUpdated+1).  That 
would force a return value of 1.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you 
are updating with the same data, than nothing changes and MySQL 
doesn't waste the time to lock the table, write the data and update 
the indexes. It's much more efficient this way.
On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something 
the MySQLDirect .NET provider is doing, and to cover that 
possibility I've sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to 
refresh it, and am getting 0 as a return value. If I send different 
data, then I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,thisname,thisdescription
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but 
really think I should be getting a 1.  I would think that if there 
was no ID with a value of 5, then it would return 0.  But if there 
is a row with an ID of 5, then it should refresh the row and return 
1.  Right?

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



--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Jeff Demel]

The issue is that the code doesn't know (and doesn't care) if the data 
is actually being changed, it's just accepting the posted form data, 
compiling it, and updating the record.  It uses the return value (number 
of rows updated) to make sure there wasn't a problem updating the 
record.  However, because of the way MySQL works, if the data matches 
what's already there then MySQL returns 0, which the code interprets as 
an Error updating the record.

So, I have to write code to see if the data posted matches the data in 
the row before running the update or add that LastUpdated work-around.

Unless, as has been suggested, there's a way to change the functionality 
of the DataProvider.

-Jeff
Brent Baisley wrote:
I'm not following why you need to force an update? You mentioned a row 
refresh, but I'm not sure in what context. If you are looking to find 
out if a row has changed since you last read it, then you should have a 
timestamp field. The first timestamp field is always updated when data 
changes in a record, so you could use this as sort of a record 
versioning system. Just query the timestamp field to check if the data 
has changed, if it has, then do the full query to retrieve the entire 
record.

Even if you create a field that you change on every update, MySQL only 
changes the data in fields that have changed, not in all the fields in 
your update statement. Paul DuBois she be able to correct if I'm wrong 
on this. I can't think of any reason to force a rewrite the same data to 
disk.

On Sep 20, 2004, at 3:49 PM, Jeff Demel wrote:
That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending 
(pull the record, compare the data, then decide to run the update or 
not). How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as an 
Int, and update that every time (TimesUpdated = TimesUpdated+1).  That 
would force a return value of 1.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you 
are updating with the same data, than nothing changes and MySQL 
doesn't waste the time to lock the table, write the data and update 
the indexes. It's much more efficient this way.
On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:

I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something 
the MySQLDirect .NET provider is doing, and to cover that 
possibility I've sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to 
refresh it, and am getting 0 as a return value. If I send different 
data, then I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,thisname,thisdescription
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but 
really think I should be getting a 1.  I would think that if there 
was no ID with a value of 5, then it would return 0.  But if there 
is a row with an ID of 5, then it should refresh the row and return 
1.  Right?

-Jeff
--
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]

Trying fulltext search

[Randy Paries]

Hello

I have a table
CREATE TABLE community_files (
  id int(3) unsigned NOT NULL auto_increment,
  type int(10) unsigned NOT NULL default '0',
  category_id int(10) unsigned NOT NULL default '0',
  filename varchar(50) NOT NULL default '',
  path varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id),
  KEY type (type),
  KEY catid (category_id),
  FULLTEXT KEY description (description)
) TYPE=MyISAM;

Where I run a query like
select * from community_files where description like '%bird%';

I get records back, but if I try to do
SELECT * FROM community_files WHERE MATCH (description) AGAINST ('bird' );
I get nothing back

I am running mysqld Ver 3.23.58 on rh9

Thanks for any help

Randy




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

SELECT question

[Andre Matos]

Hi List,

I am performing a SELECT and I am getting 0 rows when I run the SELECT
direct in the MySQL database and getting 1 when I run using PHP. This is my
select:

SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
(ScanStatusID  90 OR ScanStatusID  98);

I realized latter analyzing this select that I made a mistake using OR at
this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.
However, in both cases, I am still getting 0 rows from the database, which
is correct.

My problem is using the PHP to run the SELECT, if I use OR using the PHP, I
got 1 as a result, and if I use AND I got 0 as a result.

Is anyone can tell me what is going on?

Thanks in advance for any help.

Andre

-- 
Andre Matos
[EMAIL PROTECTED]



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

Simple query question

[John Mistler]

I have a table in which the first column is either 1 or 0.  The second
column is a number between 0 and 59.  I need to perform a query that returns
entries where:

1. IF the first column is 1, the second column is NOT 0
2. IF the first column is 0, the second column is anything.

It seems simple, but I'm not getting it right.  Any ideas?

Thanks,

John


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

Re: Simple query question

[Roger Baklund]

* John Mistler 
 I have a table in which the first column is either 1 or 0.  The second
 column is a number between 0 and 59.  I need to perform a query 
 that returns
 entries where:
 
 1. IF the first column is 1, the second column is NOT 0
 2. IF the first column is 0, the second column is anything.
 
 It seems simple, but I'm not getting it right.  Any ideas?

Try this:

SELECT * FROM tab1
  WHERE 
(col1 = 1 AND col2  0) OR 
(col1 = 0)

When combining AND and OR, proper use of parantheses is important.

-- 
Roger

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

Problems Compiling

[premal mishra]

I'm trying to use C API to MySQL.

MySQL Version: 4.0.20a
Distribution   Windows Binary
Machine:   P IV on intel 845GL board, 256 MB Ram, Win98se
Compiler:  Digital Mars C/C++ Compiler Version 8.40 
 (ftp://ftp.digitalmars.com/Digital_Mars_C++/Patch/dm840c.zip)  
   
WHEN I COMPILE THE FOLLOWING CODE:

#include sys/time.h
#include mysql.h

int main() {

return 0;
}


I GET FOLLOWING ERROR:

c:\sc\bin\sc -Ic:\mysql\include -Nc -Ae -J -c test1.cpp
  my_socket fd; /* For Perl DBI/dbd */
 ^
c:\mysql\include\mysql_com.h(116) : Error: ';' expected following declaration of 
struct member
int my_connect(my_socket s, const struct sockaddr *name, unsigned int namelen,
 ^
c:\mysql\include\mysql_com.h(180) : Error: ')' expected to close function parameter 
list with
}
^
c:\mysql\include\mysql_com.h(189) : Error: '=', ';' or ',' expected
extern C {
 ^
c:\mysql\include\mysql_com.h(223) : Error:  storage class is illegal in this context
double my_rnd(struct rand_struct *);
   ^
c:\mysql\include\mysql_com.h(228) : Error: 'my_rnd' is not in function parameter list
Fatal error: too many errors
--- errorlevel 1

--- errorlevel 1


Regards
Premal.  

Re: SELECT question

[Roger Baklund]

* Andre Matos
 I am performing a SELECT and I am getting 0 rows when I run the SELECT
 direct in the MySQL database and getting 1 when I run using PHP.
 This is my
 select:

 SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
 (ScanStatusID  90 OR ScanStatusID  98);

Looks ok.

 I realized latter analyzing this select that I made a mistake using OR at
 this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.

Are you sure about that?

 However, in both cases, I am still getting 0 rows from the database, which
 is correct.

 My problem is using the PHP to run the SELECT, if I use OR using
 the PHP, I
 got 1 as a result, and if I use AND I got 0 as a result.

This is correct, if you have one record with ScanStatusID in the range
90-98.

 Is anyone can tell me what is going on?

You seem to be misinterpreting how logical expressions work. A SQL select
statement is a description of the (sub-)set of data you wish to retrieve
from the database. This description often includes a WHERE clause,
describing wanted records, which again often includes a logical expression.
The expression is built up by operands and operators. The logical operators
relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes
one operand, the the result is the opposite of the operand. NOT true is
false, and NOT false is true. The other two operators, AND and OR, need two
operands, one on each side. For the AND operator, BOTH sides of the operator
must be true for this part of the expression to be true. For the OR
operator, ANY of the sides of the operator must be true for that part of the
expression to be true.

So, for your expression above, you can not say ...ScanStatusID  90 AND
ScanStatusID  98..., because ScanStatusID can not be below 90 AND above 98.
ScanStatusID is a single number, it can be below 90 OR above 98. Not both at
the same time.

--
Roger


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

Re: Query takes terribly long

[Dirk Schippers]

Hello Bill,
Your explanation gave me an idea:
SELECT id
FROM story
WHERE ((putdatetime='2003-09-20' AND putdatetime'2003-09-21' )
OR (putdatetime='2002-09-20' AND putdatetime'2002-09-21')
OR (putdatetime='2001-09-20' AND putdatetime'2001-09-21'))
AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30;
I know there are no stories before 2001, so I know where to stop with 
building the query. As years will pass, I don't think the query will 
become much slower with the extra OR lines added as they are all checked 
in the index.
The only thing I'm worried about with this is, what about februari 29th, 
but Mysql seems to handle (putdatetime='2002-02-29' AND 
putdatetime'2002-02-30') (yes even that) quite well), but if it turns 
out to be a problem, I'll gladly code an exception for that day.

Thanks for your tips, the query now always takes 0.03s in stead of about 
0.33s.

Dirk.
Bill Easton wrote:
Dirk,
If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.
Here's the query you asked about.
SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
 AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
 AND YEAR(putdatetime)  YEAR('2004-09-19')
 AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30
I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.
It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:
 putdatetime  '2003-09-20'
MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.
You can use
 putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)
and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.
Of course, if you really want just the data for the same day last year, you
could use
SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
 AND put=1 AND front=1
 AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30
HTH, Bill
== Dirk Schippers wrote: ==
Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long
No,
I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!
Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.
Thanks for all your help!
Dirk.
Jocelyn Fournier wrote:
 

Hi,
The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long


   

Hello,
Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that didn't change things, it might have sped
up the other queries on the table.
Probably this query can't be sped up because of the functions I use.
Is there another thing I can try to speed the query up?
And another question, is it normal that even when not using the cache
(always executing RESET QUERY CACHE; before the query), the query
sometimes only takes 0.33 seconds, and at other times the query takes 22
seconds? Is this something everyone here experiences? Or is there
something wrong with my configuration?
Regards,
Dirk.
Jocelyn Fournier wrote:

 

Hi,
I would say for this one you need an index on (put, front, putdatetime)
   

to
   

get rid of the filesorting.
Note that because you're transforming the putdatetime column with
   

functions,
   

MySQL will not be able to use index infos of putdatetime.
So it will use put and front index information to find the result, and
putdatetime to order the result.

RE: :MySQL

[Kirti S. Bajwa]

I have tried installing on LINUX syste  got errors.

Kirti

-Original Message-
From: Nathan Mealey [mailto:[EMAIL PROTECTED]
Sent: Monday, September 20, 2004 3:34 PM
To: [EMAIL PROTECTED]
Subject: DBD::MySQL


Has anyone out there had any trouble installing DBD::MySQL for MySQL 
4.1.x on Mac OS X 10.3?  I have tried it on several machines in my 
company's office, but it fails on all that are running 4.1.x, and 
installs fine on those running 4.0.x

Thoughts?

Thanks,
Nathan
--
Nathan Mealey
Director of Operations
Cycle-Smart, Inc.
P.O. Box 1482
Northampton, MA
01061-1482
[EMAIL PROTECTED]
(413) 587-3133
(413) 210-7984 Mobile
(512) 681-7043 Fax


-- 
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: SELECT question

[beacker]

Andre Matos writes:
 SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
 (ScanStatusID  90 OR ScanStatusID  98);

 I realized latter analyzing this select that I made a mistake using OR at
 this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.

The second rendition (ScanStatusID  90 AND ScanStatusID  98) will return
no rows every time.  Since ScanStatusID can not be less than 90 and greater
than 98 at the same time :)

 Something that I've found to improve readability of these kinds of
tests is to use BETWEEN(a, b)  And if you need to exclude a range the
use of NOT BETWEEN(a, b).  This way it becomes obvious what you are
looking for.
 Brad Eacker ([EMAIL PROTECTED])



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

Get lines matching a select / group by query

[Alexander Newald]

Hello,
I like to get the number of lines returned by a select ... group by query:
Example:
SELECT id FROM test WHERE LEFT(id,5) = 12345 GROUP BY value;
Perhaps I will get
+---+
| id|
+---+
| 2 |
| 1 |
| 5 |
+---+
3 rows in set (0.02 sec)
But I like to simply get 3
As I use mySQL 3.x and can't change it for now I'm not able to use 
FOUND_ROWS()

Thanks,
Alexander Newald 

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

MySQL query performance test tool

[Haitao Jiang]

Hi,

We want to test our MYSQL (4.1.4g) server's query performance, and I
just wondering if there is a tool that enable us sending a list of
queries over HTTP or JDBC repeatedly and gather/display the
statistics?

Thanks

HT

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

Re: Get lines matching a select / group by query

[Wesley Furgiuele]

Use COUNT():

SELECT COUNT( id ) FROM test WHERE LEFT( id, 5 ) = '12345' GROUP BY value;

Wes



On Tue, 21 Sep 2004 00:24:33 +0200, Alexander Newald
[EMAIL PROTECTED] wrote:
 Hello,
 
 I like to get the number of lines returned by a select ... group by query:
 
 Example:
 
 SELECT id FROM test WHERE LEFT(id,5) = 12345 GROUP BY value;
 
 Perhaps I will get
 
 +---+
 | id|
 +---+
 | 2 |
 | 1 |
 | 5 |
 +---+
 3 rows in set (0.02 sec)
 
 But I like to simply get 3
 
 As I use mySQL 3.x and can't change it for now I'm not able to use
 FOUND_ROWS()
 
 Thanks,
 
 Alexander Newald
 
 --
 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: Query takes terribly long

[Bill Easton]

Good idea.  MySQL appears to use the index and skip between intervals.  (I'm not sure 
it always did...)

On the Feb. 29, I'd be disinclined to rely on MySQL's (or any other DB's) handling of 
invalid dates.  Note that it appears to take '2002-02-29' to mean Mar. 1, 2002, and 
'2002-02-30' to mean Mar 2, 2002.  So your query below, for 2-29, would give the 
counts for Mar. 1 in the prior years.  The original query you asked about gave zero 
for 2-29.  Figure out what you want for a result--and don't forget that in 2005, there 
isn't a 2-29, so you won't ever see the values for 2004-02-29.  Maybe you only care 
about an approximate result, and none of this matters.
  - Original Message - 
  From: Dirk Schippers 
  To: Bill Easton 
  Cc: [EMAIL PROTECTED] 
  Sent: Monday, September 20, 2004 6:06 PM
  Subject: Re: Query takes terribly long


  Hello Bill,

  Your explanation gave me an idea:

  SELECT id 
  FROM story 
  WHERE ((putdatetime='2003-09-20' AND putdatetime'2003-09-21' ) 
   OR (putdatetime='2002-09-20' AND putdatetime'2002-09-21')
   OR (putdatetime='2001-09-20' AND putdatetime'2001-09-21')) 
  AND put=1 AND front=1 
  ORDER BY putdatetime DESC 
  LIMIT 0,30;

  I know there are no stories before 2001, so I know where to stop with building the 
query. As years will pass, I don't think the query will become much slower with the 
extra OR lines added as they are all checked in the index.
  The only thing I'm worried about with this is, what about februari 29th, but Mysql 
seems to handle (putdatetime='2002-02-29' AND putdatetime'2002-02-30') (yes even 
that) quite well), but if it turns out to be a problem, I'll gladly code an exception 
for that day.

  Thanks for your tips, the query now always takes 0.03s in stead of about 0.33s.

  Dirk.

  Bill Easton wrote: 
Dirk,

If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.

Here's the query you asked about.

SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
  AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
  AND YEAR(putdatetime)  YEAR('2004-09-19')
  AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30

I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.

It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:

  putdatetime  '2003-09-20'

MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.

You can use

  putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)

and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.

Of course, if you really want just the data for the same day last year, you
could use

SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
  AND put=1 AND front=1
  AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30

HTH, Bill

== Dirk Schippers wrote: ==

Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long

No,

I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!

Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.

Thanks for all your help!
Dirk.

Jocelyn Fournier wrote:

  Hi,

The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?

Regards,
 Jocelyn Fournier
 www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long




Hello,

Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that 

Re: OUTER JOIN bug in 5.0.1?

[Josh Trutwin]

On Mon, 20 Sep 2004 10:25:16 -0400
[EMAIL PROTECTED] wrote:

 I think you missed my point. I think the 5.0.1 behavior was correct
 and the others are wrong. There is a known bug (or two) about mixing
 outer joins and inner joins and it looks like it may be fixed. IF
 you want to see all of the students THAT TABLE (students) needs to
 be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT
 JOIN. That's what the directions mean

Interesting - do you have a link to more information on this bug?

 SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
 FROM student s
 INNER JOIN enrollment e 
 ON e.tech_id = s.tech_id
 AND e.term_id = '3'
 LEFT JOIN submitted_assignment sa 
 ON sa.tech_id = s.tech_id 
 LEFT JOIN assignment a 
 ON a.a_id = sa.a_id 
 AND a.a_id = '100'
 ORDER BY s.full_name;
 
 I also moved the clause AND a.a_id = '100' into the ON portion of
 the LEFT JOIN. That way you can see who did and didn't get that
 assignment.
 
 If you describe what you WANT to see, I can help you write the query
 to get it. What I think I wrote for you will be all students where
 term_ID=3 and what grades they got on assignment 100. But i think
 you may get some duplicate rows of blank scores. Does assignment
 relate to student, perhaps with a tech_id or student_id field?
 That fixes one dilemma by setting up the following query
 
 SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as 
 assigned_100, sa.points_awarded, sa.date_submitted
 FROM student s
 INNER JOIN enrollment e 
 ON e.tech_id = s.tech_id
 AND e.term_id = '3'
 LEFT JOIN assignment a 
 ON a.student_ID = s.student_ID
 AND a.a_id = '100'
 LEFT JOIN submitted_assignment sa 
 ON sa.tech_id = s.tech_id 
 AND a.a_id = sa.a_id 
 ORDER BY s.full_name;

This is what I eventually used:

SELECT s.tech_id, s.full_name, 
   sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e 
ON e.tech_id = s.tech_id
LEFT JOIN submitted_assignment sa 
   ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id'
LEFT JOIN assignment a 
   ON a.a_id = sa.a_id
WHERE e.term_id = '3'
ORDER BY s.full_name;

It didn't seem to work with the AND sa.a_id = '$a_id' in the assignment join 
condition - but this works.  I don't understand why that doesn't filter the right rows 
if it's in the WHERE clause, I usually define my JOIN condition solely on the 
column(s) being joined together and any additional filtering gets done in the WHERE 
clause.

Man, I thought I had a good handle on OUTER JOINs.  Erg.

Josh



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