Re: Alias a function result?

2007-01-24 Thread Felix Geerinckx 
[EMAIL PROTECTED] (Jerry Schwartz) wrote in
news:[EMAIL PROTECTED]: 

 SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY
 book_author WHERE book_title LIKE something;
  
 This actually seems to work, but it makes me ill to look at it. 

I doubt this works: there is no FROM clause, and the WHERE and GROUP BY 
clauses are in the wrong order.

The following will work:

SELECT book_author, GROUP_CONCAT(book_title) AS book_title
FROM foo
WHERE book_title LIKE 'SOMETHING'
GROUP BY book_author

but the book_title in the WHERE clause is *not* the alias but the 
individual column. Results of an aggregate function are *never*  
available in a WHERE clause. 

You need a HAVING clause.

-- 
felix

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

MySQL5 becomes so slowly when bin log is open

2007-01-24 Thread gulei 
MySQL5 becomes so slowly when bin log is open.

I used sysbench to test mysql.

MySQL version is mysql-standard-5.0.27-linux-i686-icc-glibc23.tar.gz
OS is RHEL 4.0

my.cnf is :

[mysqld]
#log-bin=icc
max_connections=3000
innodb_log_file_size=50M
max_prepared_stmt_count=32765
#log=Buddha.log
query_cache_size=16M
innodb_buffer_pool_size=800M
#key_buffer_size=512M

server-id=55
port=3309

I used sysbench to test mysql just like this:
sysbench --max-requests=1 --mysql-host=172.20.23.28
--mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp --oltp-table-size=1 run

It completed 396 transactions per second whithout bin log.
But when bin log is enabled, only 76 transactions per second.

I also tested MySQL5.0.33. Almost the same result.
But mysql4.1.22 didn't have that problem.

The whole sysbench's result is below:

When bin log is closed:

sysbench --max-requests=1 --mysql-host=172.20.23.28 --
mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp -
-oltp-table-size=1 run
sysbench v0.4.8:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
WARNING: Preparing of BEGIN is unsupported, using emulation
(last message repeated 49 times)
Running the test with following options:
Number of threads: 50

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned
in 75 pct cases)
Using BEGIN for starting transactions
Using auto_inc on the id column 
Maximum number of requests for OLTP test is limited to 1
Threads started!
Done.

OLTP test statistics:
queries performed:
read:140042
write:   50009
other:   20004
total:   210055
transactions:10001  (396.49 per sec.)
deadlocks:   2  (0.08 per sec.)
read/write requests: 190051 (7534.67 per sec.)
other operations:20004  (793.07 per sec.)

Test execution summary:
total time:  25.2235s
total number of events:  10001
total time taken by event execution: 1258.4014
per-request statistics:
 min:0.0301s
 avg:0.1258s
 max:0.4040s
 approx.  95 percentile: 0.1926s

Threads fairness:
events (avg/stddev):   200.0200/3.52
execution time (avg/stddev):   25.1680/0.03



When bin log is open :

sysbench --max-requests=1 --mysql-host=172.20.23.28 --
mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp -
-oltp-table-size=1 run
sysbench v0.4.8:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
WARNING: Preparing of BEGIN is unsupported, using emulation
(last message repeated 49 times)
Running the test with following options:
Number of threads: 50

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned
in 75 pct cases)
Using BEGIN for starting transactions
Using auto_inc on the id column 
Maximum number of requests for OLTP test is limited to 1
Threads started!
Done.

OLTP test statistics:
queries performed:
read:144886
write:   50743
other:   20349
total:   215978
transactions:1  (76.92 per sec.)
deadlocks:   349(2.68 per sec.)
read/write requests: 195629 (1504.68 per sec.)
other operations:20349  (156.51 per sec.)

Test execution summary:
total time:  130.0134s
total number of events:  1
total time taken by event execution: 6485.2686
per-request statistics:
 min:0.0353s
 avg:0.6485s
 max:6.0622s
 approx.  95 percentile: 1.8840s

Threads fairness:
events (avg/stddev):   200./14.86
execution time (avg/stddev):   129.7054/0.17

Are there any tips?
Or maybe I should use mysql4.1 ?

Thanks

Gu Lei


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

Re: Alias a function result?

2007-01-24 Thread Mark Leith 

Felix Geerinckx wrote:

[EMAIL PROTECTED] (Jerry Schwartz) wrote in
news:[EMAIL PROTECTED]: 

  

SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY
book_author WHERE book_title LIKE something;
 
This actually seems to work, but it makes me ill to look at it. 



I doubt this works: there is no FROM clause, and the WHERE and GROUP BY 
clauses are in the wrong order.


The following will work:

SELECT book_author, GROUP_CONCAT(book_title) AS book_title
FROM foo
WHERE book_title LIKE 'SOMETHING'
GROUP BY book_author

but the book_title in the WHERE clause is *not* the alias but the 
individual column. Results of an aggregate function are *never*  
available in a WHERE clause. 


You need a HAVING clause.

  
Ahh indeed, I was only looking at the aliasing of the group concat 
function. :)


Teach me for skimming emails!

Mark

--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification


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

Innodb Error 1030

2007-01-24 Thread Gary Huntress 

I'm trying to fix a problem with a users innodb database (mysql 5.0).When they try a 
very simple insert they get ERROR 1030 (HY000): Got error -1 from storage 
engine  I have dumped and reloaded the tables but the problem persists.   CHECK 
TABLE does not indicate an error.

I stopped and restarted the server once normally, without attempting any innodb_force_recovery options. 

There are no other unusual indications on the server.   It has not been shut down abruptly recently, the load is low, memory and disk space is fine.   


Does anyone have any suggestions for correcting this error?



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

[urgent] - Problem with index_merge

2007-01-24 Thread Ratheesh K J 
Hello all,

I have struck with a big problem with MySQL 5.0.22 server on RHEL 3.

After an upgradation from MySQL 4.1.11 to MySQL 5.0.22 almost all the queries 
are struggling to execute and the DB server is clogged. Below is an example of 
what is happening.

This query usd to execute very fast on MySQL 4.1.11 (in about less than 10 
seconds). But on MySQL 5.0.22 it is taking an eternity. Running an EXPLAIN 
shows that an index_merge is being used as shown below in the EXPLAIN result. 
On MySQL 4.1.11 the the PRIMARY key was being used as the index as shown in the 
second query(The same query with FORCE index on PRIMARY). I am really confused 
as to why it is taking such a long time to execute when an index_merge is being 
used. As we can see that the number of row scans using index_merge is way too 
less when compared to the second query.

Why is the first query so slow when compared to the second one even if the 
number of rows to be examined is too less in the former?

Is this a bug in index_merge?

And we have atleast 15 such queries always running on the system. The server is 
clogged !!

Query with index_merge

# Execution time : 53 seconds
EXPLAIN 
SELECT 
SUM(1) AS ELE13, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14, 
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) 
AS ELE15, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17, 
SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND 
TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19, 
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND 
TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS 
ELE21, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS 
ELE22 

FROM TBL_FORUMS_MSG_MAIN TFMM 

WHERE TFMM.FLD_ACC_ID in (6) 
AND TFMM.FLD_PARENT_ID = 0 
AND TFMM.FLD_MSG_ID  0 
AND TFMM.FLD_MARK_AS_DELETED = 0 
AND TFMM.FLD_BLOCK_STATE = 0 
AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 
AND TFMM.FLD_ESCALATED_FLAG = 0 
AND TFMM.FLD_ADD_DATE_TIME = '2007-01-23 00:00:00' 
AND TFMM.FLD_ADD_DATE_TIME ='2007-01-23 23:59:00' 
ORDER BY TFMM.FLD_ADD_DATE_TIME
;
-
*** row 1 ***
  table:  TFMM
   type:  index_merge
  possible_keys:  
PRIMARY,FLD_PARENT_ID,FLD_ADD_DATE_TIME,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG
key:  
FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG
key_len:  4,2,2,2,2,2
ref:  NULL
   rows:  10170
  Extra:  Using 
intersect(FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG);
 Using where
-


Query with FORCE INDEX(PRIMARY)

# Execution time : 13 seconds
EXPLAIN 
SELECT 
SUM(1) AS ELE13, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14, 
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) 
AS ELE15, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17, 
SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND 
TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19, 
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND 
TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS 
ELE21, 
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS 
ELE22 

FROM TBL_FORUMS_MSG_MAIN TFMM  FORCE INDEX(PRIMARY)

WHERE TFMM.FLD_ACC_ID in (6) 
AND TFMM.FLD_PARENT_ID = 0 
AND TFMM.FLD_MSG_ID  0 
AND TFMM.FLD_MARK_AS_DELETED = 0 
AND TFMM.FLD_BLOCK_STATE = 0 
AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 
AND TFMM.FLD_ESCALATED_FLAG = 0 
AND TFMM.FLD_ADD_DATE_TIME = '2007-01-23 00:00:00' 
AND TFMM.FLD_ADD_DATE_TIME ='2007-01-23 23:59:00' 
ORDER BY TFMM.FLD_ADD_DATE_TIME
;

RE: Alias a function result?

2007-01-24 Thread Jerry Schwartz 
I saw a reference that said standard SQL doesn't allow an alias to be used
in a WHERE clause, but I thought perhaps I'd get an error message. I
understand why, logically, it should be the way it is.

Thanks for the reassurance.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Mark Leith [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, January 23, 2007 4:46 PM
 To: Jerry Schwartz
 Cc: mysql@lists.mysql.com
 Subject: Re: Alias a function result?

 Jerry Schwartz wrote:
  I know that you can alias a function result as easily as
 anything else, but
  I've run into a problem with an application I inherited.
 For reasons too
  murky to go into, I would like to refer to the result of a
 group function as
  the name of the argument of the function. Here's an example
 of what I mean:
 
  SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY
  book_author WHERE book_title LIKE something;
 
  This actually seems to work, but it makes me ill to look at
 it. Is this
  legal, or have I found a loophole that might be closed in
 the future?
 

 Perfectly legal, will not get changed. :)

 Cheers,

 Mark

 --
 Mark Leith, Support Engineer
 MySQL AB, Worcester, England, www.mysql.com
 Are you MySQL certified?  www.mysql.com/certification


 --
 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: Alias a function result?

2007-01-24 Thread Jerry Schwartz 
You are correct, I mangled the actual query. What I meant to type was

SELECT book_author, GROUP_CONCAT(book_title) AS book_title FROM books WHERE
book_title LIKE something GROUP BY book_author;

The key question was about the use of the true name of a column in the WHERE
clause while using an alias of the same name in the list of columns to be
retrieved. Apparently this is the way SQL defines it to work.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Felix Geerinckx [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, January 24, 2007 3:21 AM
 To: mysql@lists.mysql.com
 Subject: Re: Alias a function result?

 [EMAIL PROTECTED] (Jerry Schwartz) wrote in
 news:[EMAIL PROTECTED]:

  SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY
  book_author WHERE book_title LIKE something;
 
  This actually seems to work, but it makes me ill to look at it.

 I doubt this works: there is no FROM clause, and the WHERE
 and GROUP BY
 clauses are in the wrong order.

 The following will work:

 SELECT book_author, GROUP_CONCAT(book_title) AS book_title
 FROM foo
 WHERE book_title LIKE 'SOMETHING'
 GROUP BY book_author

 but the book_title in the WHERE clause is *not* the alias but the
 individual column. Results of an aggregate function are *never*
 available in a WHERE clause.

 You need a HAVING clause.

 --
 felix

 --
 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: After backup/restore view turns to table

2007-01-24 Thread Davor Dundovic 



Hi All !

I did mysqldump mydb -uroot -ppass --databases --add-locks 
--allow-keywords --create-options --extended-insert --routines -r file.sql


and after that in MySQL Command Line Client source file.sql

After that my view was converted to an empty table.

What should I do to backup and restore my view correctly ?



Regards, Dundo.




Nobody knows how to backup and then restore views ?

MySQL version is 5.0.27, db type is InnoDB.


Regards, Dundo.



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

Re: speeding up a join COUNT

2007-01-24 Thread Brent Baisley 
You should create indexes on the fields you search on most. In this case, you are searching on the user_type field, so create an 
index on that field. Otherwise you need to scan the entire table to find out which users are of the type you are searching for.



- Original Message - 
From: James Tu [EMAIL PROTECTED]

To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, January 23, 2007 12:04 PM
Subject: speeding up a join COUNT



I'm performance testing my 'users' table.  It currently has roughly  1M user 
records.  The 'geo_entities' table has ~ 250 records.

Here's my query.

SELECT users.entity_id, geo_entities.entity_name,  geo_entities.short_code, 
COUNT( users.entity_id )
FROM users, geo_entities
WHERE users.user_type = 'user'
AND users.entity_id = geo_entities.id
GROUP BY entity_id
LIMIT 0 , 30

It took 51 seconds to execute.

Both tables only have an index on their unique record id.
Is there a way to speed up this up?

-James


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

best way to query this table

2007-01-24 Thread Randy Paries 

Hello,
I have a table that has a column that has a timestamp Column.

This table is going to get very large ( 1 or 2 million rows) and will
be queried alot.

Alot of these queries will be against timestamp column.

These queries will be like, getting entries for today, this week and this month

I have indexed the column, but i am wondering if i should create a
couple of int columns that would be day of week, week of year and
month and query against these instead.

would love you opinions

thanks
Randy

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

Re: New DBManager Released

2007-01-24 Thread Ed Reed 
Yes it is true.
 
I downloaded it and installed it to try and manage my user accounts. In the 
feature list it says Object Maintenance and User Objects are listed. But when 
you try to manage a user account I get a message saying that the feature is not 
available in this version.
 
- enjoy

 COS [EMAIL PROTECTED] 1/24/07 4:08 AM 
Hi,

Not true, please read http://www.dbtools.com.br/EN/dbmanagerpro/features.php 
(present in my original post) for details.

Best Regards,

DBTools Software
http://www.dbtools.com.br ( http://www.dbtools.com.br/ )


- Original Message - 
From: Ed Reed [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com; PostgreSQL Announce List
pgsql-announce@postgresql.org; SQlite Users Group
sqlite-users@sqlite.org; Firebird Tools [EMAIL PROTECTED]
Sent: Tuesday, January 23, 2007 3:20 PM
Subject: Re: New DBManager Released


The free version is crippled. You don't get all the capabilities they say.


 COS [EMAIL PROTECTED] 1/23/07 8:16 AM 
Hi,

DBTools Software is proud to announce the availability of the new DBManager
Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2.
For a complete list of changes please check our website at
http://www.dbtools.com.br/EN.

The new versions are available in our downloads center at
http://www.dbtools.com.br/EN/downloads.

What is DBTools Manager?

DBTools manager is an application for database managementm supporting MySQL,
PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server,
Sybase, Oracle and ODBC Datasources. This is a simple list of its features:

- Full object management
- Query Builder with planning, debugging capabilities
- Diagram Designer
- Report and Form Builders
- Lots of Wizards to import and export data/structure to/from a variety of
sources
- Database Documenter, Comparer and Migration Wizards
- View, Procedure and Function Builder
- English and Portuguese BR language resources available
- and much more

Available in two editions: Enteprise and Standard. The latest can be used
for free for personal use.
For a complete list of its features check
http://www.dbtools.com.br/EN/dbmanagerpro/features.php.

Best Regards,

DBTools Software
http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) ( 
http://www.dbtools.com.br/ )


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

Re: best way to query this table

2007-01-24 Thread Martijn Tonies 
Hello Randy,

 I have a table that has a column that has a timestamp Column.

 This table is going to get very large ( 1 or 2 million rows) and will
 be queried alot.

 Alot of these queries will be against timestamp column.

 These queries will be like, getting entries for today, this week and this
month

 I have indexed the column, but i am wondering if i should create a
 couple of int columns that would be day of week, week of year and
 month and query against these instead.

 would love you opinions

Indices are all about selectivity, the higher the selectivity, the better.

The maximum selectitivy is 1, that means that for each row, there's
a unique value.

If you created columns for days/months, the selectivity for each of
these will be lower than the selectivity for the timestamp column.

So an index on the timestamp column is better. Make sure that your
query uses the index and you should be doing well.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: best way to query this table

2007-01-24 Thread Miles Thompson 

At 03:31 PM 1/24/2007, Martijn Tonies wrote:


Hello Randy,

 I have a table that has a column that has a timestamp Column.

 This table is going to get very large ( 1 or 2 million rows) and will
 be queried alot.

 Alot of these queries will be against timestamp column.

 These queries will be like, getting entries for today, this week and this
month

 I have indexed the column, but i am wondering if i should create a
 couple of int columns that would be day of week, week of year and
 month and query against these instead.

 would love you opinions

Indices are all about selectivity, the higher the selectivity, the better.

The maximum selectitivy is 1, that means that for each row, there's
a unique value.

If you created columns for days/months, the selectivity for each of
these will be lower than the selectivity for the timestamp column.

So an index on the timestamp column is better. Make sure that your
query uses the index and you should be doing well.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


And all I would add to that, is make certain your queries cover date ranges 
so you maximize use of the index.


Sounds obvious, I know. We sometimes forget the obvious.

Cheers - Miles


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007



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

mysqlmanager safe?

2007-01-24 Thread David Sparks 
I've switched over to the mysqlmanager startup system instead of the old
mysqld_safe because thats the only supported method in mysql5.

I needed to restart a DB so I did a `/etc/init.d/mysqlmanager restart`
which seemed to work, but there were some problems:

- the daemon was no longer accepting connections
- ps showed 2 copies of mysqld running

I also noticed a lot of errors like this in mysqld.err:

070124 15:27:02 [ERROR] /usr/sbin/mysqld: Incorrect information in file:
'./databasename/table.frm'

One of the daemon processes would not respond to kill so eventually I
fired up gdb and killed it (it was stuck on a futex operation).  I then
restarted mysql and it went through a huge crash recovery.

A co-worker tells me that mysqlmanager has been known to do this for
ages.  Is this true?  The DB in question is all InnoDB, approx 150GB in
12 tables.  mysql version 5.0.30.

Thanks!

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

Re: speeding up a join COUNT

2007-01-24 Thread Alex Arul 

and also an index on users.entity_id (will help the join) should solve your
problem.

Thanks
Alex

On 1/24/07, Brent Baisley [EMAIL PROTECTED] wrote:


You should create indexes on the fields you search on most. In this case,
you are searching on the user_type field, so create an
index on that field. Otherwise you need to scan the entire table to find
out which users are of the type you are searching for.


- Original Message -
From: James Tu [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, January 23, 2007 12:04 PM
Subject: speeding up a join COUNT


 I'm performance testing my 'users' table.  It currently has roughly  1M
user records.  The 'geo_entities' table has ~ 250 records.

 Here's my query.

 SELECT users.entity_id,
geo_entities.entity_name,  geo_entities.short_code, COUNT( users.entity_id)
 FROM users, geo_entities
 WHERE users.user_type = 'user'
 AND users.entity_id = geo_entities.id
 GROUP BY entity_id
 LIMIT 0 , 30

 It took 51 seconds to execute.

 Both tables only have an index on their unique record id.
 Is there a way to speed up this up?

 -James


 --
 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: MySql Version difference

2007-01-24 Thread Peter Brawley 

How could i know the difference between these 2 version and there
compatiblity.


Read the Change History section of the manual for versions 5.0.19 
through 5.0.27.


PB

-

Nilesh wrote:

Hi,

I have a webapplication developed using J2EE. For the application, the

database is on MySQL 5.0.18 version . Now I plan to use the lastest MySQL
version (5.0.27). But before that i want to know the differences between
these 2 versions and whether the current database will work fine with 5.0.27
version.
How could i know the difference between these 2 version and there
compatiblity.
 
Thanks and Regards,

Nilesh

  



No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007
  
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007


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

RE: MySql Version difference

2007-01-24 Thread Nilesh 
Thanks Peter.
But is there any other way to do that. Can't i directly compare the 2
version, instead of reading Change Histroy section for all the version in
between.
 
Regards,
Nilesh
 



  _  

From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 25, 2007 11:56 AM
To: Nilesh
Cc: mysql@lists.mysql.com
Subject: Re: MySql Version difference


How could i know the difference between these 2 version and there

compatiblity.
Read the Change History section of the manual for versions 5.0.19 through
5.0.27.

PB

-

Nilesh wrote: 

Hi,



I have a webapplication developed using J2EE. For the application, the

database is on MySQL 5.0.18 version . Now I plan to use the lastest MySQL

version (5.0.27). But before that i want to know the differences between

these 2 versions and whether the current database will work fine with 5.0.27

version.

How could i know the difference between these 2 version and there

compatiblity.

 

Thanks and Regards,

Nilesh



  


  _  


No virus found in this incoming message.

Checked by AVG Free Edition.

Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007