Re: How to utilize 16 logical CPUs

2006-08-22 Thread Martin Jespersen
Your mysql db is only using 496Mb of ram? i have a server with 1GB ram where mysql uses more mem than yours does - you should probably tune your server system variables a bit to utilize your memory better. Christian Hammers wrote: On Tue, Aug 22, 2006 at 10:20:47AM -0600, Michael Loftis

Re: Another question on Cardinality??

2006-08-04 Thread Martin Jespersen
Have you considered reading up on basic database management? There are plenty of good material on the web for you to read where you can actually learn how to manage databases, so you don't have to ask others about every single detail. Ratheesh K J wrote: Hello all, Another question on

Re: FW: How do I find all the users that are new since my last login (repost)

2006-08-04 Thread Martin Jespersen
You could add an extra field called last_login_date which you'd set only once per session - at login time. At login time you'd set this to the value that exists in login_date. Then use that for comparison against created_on. Daevid Vincent wrote: I have a SQL challenge I'm not sure how to

Re: Table analysis - Help required urgently

2006-08-03 Thread Martin Jespersen
If i was you i'd start reading the manual. Look under the optimization chapter. Also, brush up on database normalization and look into how exactly the tables are being used - run explains on the sql statements that are used with the tables and see how you can optimize index usage. Ratheesh K

Re: Fatal error

2006-08-03 Thread Martin Jespersen
, Martin Jespersen [EMAIL PROTECTED] wrote: Looks like drupal had an error when doing the backup - mysql is complaining over the fact that your backup.sql file contains a html formated fatal error message. Kaushal Shriyan wrote: Hi ALL I have taken backup of http://mydomain.com/?q=admin/database

Re: Doing a join

2006-08-02 Thread Martin Jespersen
select g.GROUP_NAME, count(mg.MEM_ID) as NUMBER_OF_MEMBERS from GROUPS g left join MEM_GRO mg using(GRO_ID) group by g.GRO_ID John Meyer wrote: I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this

Re: FULL TEXT SEARCH ALTERNATIVES...

2006-08-02 Thread Martin Jespersen
You can tune the fulltext search in a few ways using the config, try read up on the various server variables that has to do with fulltext indexing. Other than that there is always the option of upgrading the hardware :) [EMAIL PROTECTED] wrote: Hi! I'm getting a lot of pushback on using

Re: Multiple single column indexes

2006-08-02 Thread Martin Jespersen
It depends what you need and how your data looks. Say you have a table with 4 columns: col1 has 1000 dictinct values col2 has 1 dictinct values col3 has 100 dictinct values col4 has 10 dictinct values In this case: select col1,col2 from tbl where col1=... and col2=... Having a single

Re: Fatal error

2006-08-02 Thread Martin Jespersen
Looks like drupal had an error when doing the backup - mysql is complaining over the fact that your backup.sql file contains a html formated fatal error message. Kaushal Shriyan wrote: Hi ALL I have taken backup of http://mydomain.com/?q=admin/database through drupal 4.6.3 by selecting all

swapping column values in update

2006-08-01 Thread Martin Jespersen
I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this

Re: swapping column values in update

2006-08-01 Thread Martin Jespersen
it's a frequent operation based on a where clause Barry Newton wrote: At 06:35 PM 8/1/2006, Martin Jespersen wrote: I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1

Re: Problems with WHERE clause

2006-07-30 Thread Martin Jespersen
Do the right thing(TM) and don't use an alias that is the same as a field that exists (this was mentioned before by someone else). Since there is a field in your join named fee, using fee as an alias, is bound to cause your trouble. Also, trying to call sum(p.fee) twice is a waste of cputime. I

Re: optimize mysql geometric query

2006-07-28 Thread Martin Jespersen
Try to run an explain on the query to see how mysql optimizes it. Also have a look at your system resources while the query runs to see if you have a problem with memory/swapping. I am not sure (since i've never used union), but my guess is that mysql uses temporary tables for this, so maybe

Re: What does optimize mean?

2006-07-28 Thread Martin Jespersen
read the manual: http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html Jesse wrote: Seeing posts about mysqlcheck on the list prompted me to check out that utility. A lot of it I can see is very useful, and I understand what it does. However, what does the optimize command mean? What

Re: MySQL performing too badly under heavy load - urgent hlp needed

2006-07-27 Thread Martin Jespersen
I doubt apache is to blame. 5 seconds for a query on a website is extremely slow, so if that is your normal results, then you have a problem there already. I've been building database driven websites for around 11 years and i don't think i can remember a single time i went into production with

Re: Checkboxes

2006-07-26 Thread Martin Jespersen
Looks like more of a php question that a mysql question ;) anyway, the mysql part: use either an unsigned interger (tinyint, if less than 256 possibilities, smallint if over 256 but less than 65565, etc) or use a set or enum. Using a varchar is not really the way to go. If you choose to

Re: Select Query taking time

2006-07-24 Thread Martin Jespersen
Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running

Re: Searching through an alphabetical range

2006-07-24 Thread Martin Jespersen
Depending on the size of your table, it can be faster using SELECT lastname FROM employee WHERE lastname BETWEEN 'm' AND 'z' GROUP BY lastname; On a table with 2,5 Mill. records the speed diff is over 1000% on my system. ViSolve DB Team wrote: Hello Paul, You can try this: SELECT

Re: Struggling with the logic

2006-07-24 Thread Martin Jespersen
As mentioned before you really should sue the date data type in mysql, or alternativly use an int and store the date as seconds since the epox, so that you can do simple math for this type of query. Any other way of storing dates is basically shooting yourself in the foot. That said, if you

Re: database back up

2006-07-20 Thread Martin Jespersen
You can usually find the database files under the var subdirectory under your installation, unless another datadir was specified at compiletime. ;) Martin Joko Siswanto wrote: Dear All if myqsl service can't start, where can i found the file and back up it? [under windows and linux]

Re: How to query on part of a date column?

2006-07-20 Thread Martin Jespersen
As long as backticks are used around fieldnames, spaces and/or reserved words are fine, tho it does tend to create more work for the user ;) mos wrote: At 12:02 PM 7/20/2006, you wrote: I've got a table of people who registered for a convention. Each person has a registration date, kept in a

Re: 3.23.58 - 5.0.22 upgrade

2006-07-18 Thread Martin Jespersen
Using dumps are almost always the way to go, upgrading the datafiles themselves is something you should avoid unless you are into heavy wizardry and/or pain. Dan Trainor wrote: Martin Jespersen wrote: Dan has a very good point, be mindfull of the changed password algorithm, that actually

Re: 3.23.58 - 5.0.22 upgrade

2006-07-13 Thread Martin Jespersen
I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source command. Dan Trainor wrote: Hi - I know we've rolled this around the list a few

Re: 3.23.58 - 5.0.22 upgrade

2006-07-13 Thread Martin Jespersen
that came about with 4.1. Dan On 7/13/06, Martin Jespersen [EMAIL PROTECTED] wrote: I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source

Re: very basic questions

2006-07-09 Thread Martin Jespersen
1: .sql files are usually textfiles with sql statements in them delimited by ; 2: do the following 1) start the client - /path/to/mysql -u user -ppass 2) select your database (if nescessary) - use db 3) import the sql file - source /path/to/fill_help_tables.sql [EMAIL PROTECTED] wrote:

space usage

2006-07-05 Thread Martin Jespersen
the maintable instead of a seperate table so i can avoid slow joins. what will be better for queryspeed/size: adding them with NULL using NULL as default or with NOT NULL using 0 and '' as defaults? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http

Re: Backup questions

2006-07-03 Thread Martin Jespersen
Jesse wrote: my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, Funny, i've never seen one that does? What system/editor are you using? or even better, if there is a way

Re: Backup questions

2006-07-03 Thread Martin Jespersen
Jesse wrote: Funny, i've never seen one that does? What system/editor are you using? Multi-Edit version 8.0i. This is an older version of the editor. Maybe a newer one wouldn't, but for the most part, it does a very good job for me. Ok never heard of multiedit... if your system is

Re: Full Text Search across 2 tables.

2006-07-01 Thread Martin Jespersen
Maybe something like this: select ft.topic, fm.message from forums_topics ft, forums_messages fm match (ft.topic, fm.message) against (...) if topic is null then the hit is from fm and vice versa... haven't tried it, so might not work :) Steffan A. Cline wrote: I have 2 tables which have

Re: is there a way to optimize like '%..%' searches ?

2006-06-30 Thread Martin Jespersen
that need a string search? Dan On 6/28/06, Martin Jespersen [EMAIL PROTECTED] wrote: I was too fast there it seems fulltext searches doesn't help after all since i can't use leading wildcards to words :( too bad i loved the speed :/ -- MySQL General Mailing List For list archives: http

is there a way to optimize like '%..%' searches ?

2006-06-28 Thread Martin Jespersen
read_rnd_buffer (32M) and sort_buffer (32M) but this query is still slow as hell... Any hints as to how i can optimize the query? or tune my server settings to produce faster results? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: is there a way to optimize like '%..%' searches ?

2006-06-28 Thread Martin Jespersen
significantly. Date, type of document, author, etc. Even if it were an optional criteria for people it might speed up at least some of the searches. Dan On 6/28/06, Martin Jespersen [EMAIL PROTECTED] wrote: Hey all i am running mysql 4.1.20. I have a table with about 2.5 million records and i

Re: is there a way to optimize like '%..%' searches ?

2006-06-28 Thread Martin Jespersen
I was too fast there it seems fulltext searches doesn't help after all since i can't use leading wildcards to words :( too bad i loved the speed :/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

assembler not found or what?

2006-06-24 Thread Martin Jespersen
functions, even tho /usr/bin/as is in the path. anyone have any idea why? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Just need script for creating tables

2006-06-21 Thread Martin Jespersen
--no-data should do the trick, try to do mysqldump --help and read the output Xiaobo Chen wrote: Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create

Problems when compiling the source

2006-06-20 Thread Martin Jespersen
Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems when compiling the source

2006-06-20 Thread Martin Jespersen
pm, Martin Jespersen wrote: ./configure --prefix=/opt/.mysql-4.1.20 --enable-assembler --enable-thread-safe-client --enable-static=all --with-gnu-ld --with-mysqld-user=mysql --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-charset=latin1 --with-collation