How to check whae are tables are having how many indexes on which columns ?
Thomas Sundberg <[EMAIL PROTECTED]> wrote:
> -Original Message-
> From: Seena Blace [mailto:[EMAIL PROTECTED]
> Sent: den 11 maj 2005 23:58
> To: mysql@lists.mysql.com
> Subject: table optimisation
>
> Hi,
> I ha
- Original Message -
From: "Neculai Macarie" <[EMAIL PROTECTED]>
To: "Mysql"
Sent: Thursday, May 12, 2005 1:20 PM
Subject: Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try i
> -Original Message-
> From: Seena Blace [mailto:[EMAIL PROTECTED]
> Sent: den 11 maj 2005 23:58
> To: mysql@lists.mysql.com
> Subject: table optimisation
>
> Hi,
> I have been noticing table performanace issue when # of rows
> grows more.How to tune that table?
Adding index on the col
> Not that I'm aware of. What type of conversions are you doing that you
> need 30,000 use vars? An easy solution would be to try it and find out :)
I need to move multiple linked entries (in around 12 tables) from one
running server to another. I'm using auto_increment's all over the place and
I
With Mysql you should ONLY use RAID10. Everything else is not worth your
time.
As long as you are using 15k SCSI drives, on both your master and your
slave, your slave should rarely ever fall behind. Especially if you are
doing less than 1,000 inserts per second on the master. Otherwise you
s
Mike Johnson wrote:
I think I'm just having a brain fart here, but if someone could help me
out I'd appreciate. Maybe it's just too late in the day for thinking...
Say I have a table `actions` with a structure and data as such:
++++
| id | userid | action |
+++--
i solved the problem by installing mysql-4.1.11-0 the rpm version the server
and the client
then there is no problem of the missing mysql.socl file problem
http://graphics.hotmail.com/emarrow_right.gif";
width=16>Ganesan_Malairajahttp://graphics.hotmail.com/emarrow_left.gif";
width=16>
--
M
query runs for 5 min... kill query id.
On 12 May 2005 00:29:56 +0200, Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>,
> Eric Jensen <[EMAIL PROTECTED]> writes:
>
> > So you want 5 contacts for every user? Try this:
> > SELECT COUNT(c.id) AS count, u.username, u.firs
If you're running in a master/slave environment.. and you're application
is using the slave too often... replication can fall behind which can
then confuse your application.
This can happen if the IO performance of both the master and slaves is
equivalent and you're performaning INSERT/UPDATE/D
Hi,
I want report like this
Table description is like as follows
Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+-++
| id | int(
Hi,
I have been noticing table performanace issue when # of rows grows more.How to
tune that table?
thanks
-Seena
-
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
Kevin,
I am in the same boat that you are, I can't store anything in memory, just
have too much data. I've got 2tb on one box right now, I did get a quote
last week for that much memory, I think it was 4 million just for the
memory.
> Also.. if you have a high cache hit rate you can effectively h
Hi,
I've already seen this problem with gcc 4.0, I have to change
size_socket declaration to socklen_t.
Anyway, you won't be able to compile properly MySQL because of a bug in
gcc 4.0 which will be fixed in 4.0.1.
Take a look at
http://gcc.gnu.org/bugzilla/show_bug.cgi?id=21173
for more details.
I think I'm just having a brain fart here, but if someone could help me
out I'd appreciate. Maybe it's just too late in the day for thinking...
Say I have a table `actions` with a structure and data as such:
++++
| id | userid | action |
++++
| 1 | a
In article <[EMAIL PROTECTED]>,
Eric Jensen <[EMAIL PROTECTED]> writes:
> So you want 5 contacts for every user? Try this:
> SELECT COUNT(c.id) AS count, u.username, u.first_name, u.last_name,
> c.name
> FROM user AS u, contact AS c
> WHERE u.id = c.id_user
> GROUP BY c.id_user
> HAVING count
Has anyone else tried to compile 4.1.11 on OS 10.4? My compilation fails on
mysqldd.cc:
mysqld.cc: In function `int bootstrap(FILE*)':
mysqld.cc:3350: warning: converting of negative value '-0x1' to
'ulong'
mysqld.cc: In function `void* handle_connections_sockets(void*)':
mysqld.
Most helpfull! Thanks!
This brings me to my next question I have a table that looks like this:
CREATE TABLE `Article_Search` (
> `ArticleID` int(11) NOT NULL default '0',
> `Content` text NOT NULL,
> PRIMARY KEY (`ArticleID`),
> FULLTEXT KEY `Content` (`Content`)
> ) ENGINE=MyISAM DEFAULT CHARSET
>
> Use one big table. A merge table will run the same query over
> all 10 tables. The key buffer is filled from the top down so
He is using a fulltext index he can't use merge tables.
If he where to UNION across the tables being used assuming he uses the
tables that only have the data he wou
Hi
I got a reply offlist from Shawn Green telling me to check my indexes on table
pending_cart.
As it turned out, the primary index (id) did not have its auto_increment bit
set.
ALTER TABLE `pending_cart`
CHANGE `id`
`id` INT( 11 )
DEFAULT '0'
NOT NULL
Andy Pieters <[EMAIL PROTECTED]> wrote on 05/11/2005 04:41:05 PM:
> Hi all
>
> I want to 'copy' the contents of the table 'cart', where userid=... to
the
> table pending_cart.
>
> Here is some example data for the table 'cart'
> ++++-+
> | id | userid | prodid | qty |
>
Dathan Pattishall wrote:
Forget using drives all together for heavy hit applications.
Build data that can fit on a ram Drive (8GB) then your able to do 20K
Not everyone can run in this config... We have way more data than we
can casually story in memory. It would just be cost prohibitive.
Mem
Use one big table. A merge table will run the same query over all 10
tables. The key buffer is filled from the top down so if you have a key
buffer that looks like this:
a
/ \
/\
b c
/ \/ \
de fg
Almos
Hi all
I want to 'copy' the contents of the table 'cart', where userid=... to the
table pending_cart.
Here is some example data for the table 'cart'
++++-+
| id | userid | prodid | qty |
++++-+
| 25 |123 | 15 | 1 |
| 23 |124 | 14
Forget using drives all together for heavy hit applications.
Build data that can fit on a ram Drive (8GB) then your able to do 20K
qps.
For instance, have a main master that holds a majority of tables call it
MASTER. Then a sub master that holds the tables which you desire to run
out of memory,
On some boxes we do more. Some we do less.
DVP
Dathan Vance Pattishall http://www.friendster.com
> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 11, 2005 12:24 PM
> To: Dathan Pattishall
> Cc: Jochem van Dieten; mysql@lists.mysql.co
DVP
Dathan Vance Pattishall http://www.friendster.com
> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 11, 2005 12:10 PM
> To: Dathan Pattishall
> Cc: [EMAIL PROTECTED]; Jochem van Dieten; mysql@lists.mysql.com
> Subject: Re: Opteron
Is there a question in there or are you just making a statement?
I'll make a statement myself.
The big difference between ATA and SCSI is command queueing. That's
really where the performance difference comes from. Basically, command
queueing means the drive has some intelligence about handling
Were kicking around using SATA drives in software RAID0 config.
The price diff is significant. You can also get SATA drives in 10k RPM
form now.,
Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
See irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.ro
Dathan Pattishall wrote:
We do about 70K qps at peak for about 1 Billion Queries per day (only on
30 servers BOOYA). So, it's pretty stable.
Also... based on my math.. this yields ~ 2300 qps per MySQL box...
which is pretty good.
Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.co
Dathan Pattishall wrote:
Are you using NPTL?
No that sucks we use the other one. Can't make a static build with NPTL.
What type of performance boost are you getting from running a static build.
Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
See irc.freenode.net #rojo if you
Use 10 smaller tables and perform a union. It's faster to look up in
smaller tables then larger ones-generally. Additionally more of the
key_buffer can be used for tables with the most hits over the tables
with less hits, making the lookup sustain speed over time.
> -Original Message-
>
Paul DuBois wrote:
At 18:36 + 5/11/05, shaun thornburgh wrote:
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
-> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
-> FROM CSV_Upload_Data
-> WHERE CSV_File = '
Hi everyone,
I have a question regarding the performance of UNION queries:
I need to do a full-text search against a large number of rows. Is it
faster to have one table with 10,000,000 text rows and perform one
full-text search. Or, am I better off having 10 smaller more managable
tables and per
I would guess it's because you can't use an aggregate function in where, but
only in having. So use select group by PRACT_NUMBER having
COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1.
The reason is that where is applied before the count is done, whereas having
after that. And you can't select by
At 18:36 + 5/11/05, shaun thornburgh wrote:
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
-> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
-> FROM CSV_Upload_Data
-> WHERE CSV_File = 'ICS'
-> AND CHAR
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
-> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
-> FROM CSV_Upload_Data
-> WHERE CSV_File = 'ICS'
-> AND CHAR_LENGTH(PRACT_NUMBER) > 4
-> AND COUNT(SUBSTR
At 08:50 AM 5/11/2005, you wrote:
Steve Buehler <[EMAIL PROTECTED]> wrote on 05/11/2005 09:36:44 AM:
>I am trying to figure something out about the compress function. If I
> wanted to compress a field in a column/row and write it back to the table,
> how can I do this? Is it possible to do it
Gleb Paharenko wrote:
Hello.
Unicode is usually used in such cases.
Mike Blezien <[EMAIL PROTECTED]> wrote:
Hello,
we are working on a project that will be using multiple languages to be
displayed and stored in a database, for producing various text strings in
english and the language associted w
Hello.
Unicode is usually used in such cases.
Mike Blezien <[EMAIL PROTECTED]> wrote:
> Hello,
>
> we are working on a project that will be using multiple languages to be
> displayed and stored in a database, for producing various text strings in
> english and the language associted w
The default timestamp display format changed between mysql 4.0 and 4.1
The easy fix is to add + 0 to all timestamp fields in selects. Like
this: select my_ts + 0 from t;
You should always read the upgrade notes in the manual. This is very well
documented:
http://dev.mysql.com/doc/mysql/en/upgradin
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out :)
-Eric
Neculai Macarie wrote:
Hi!
What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one serv
Ezequiel,
Tambien hay una lista de mysql en Espagnol.
:-)
-Original Message-
From: Johan H��k <[EMAIL PROTECTED]>
Sent: May 11, 2005 4:35 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: just hi
Hi Ezequiel,
see:
http://dev.mysql.com/doc/
/Johan
Ezequiel Rodriguez wrote
[snip]
Yes I do enjoy every day in which the manual makes my life easier. I
guess I
didn't work my question specifically enough however. I was actually
looking
for a way to 'ALTER' the table so that my State field data is always
upper.
In MSSQL you can apply a function to a field, which will then r
Yes I do enjoy every day in which the manual makes my life easier. I guess I
didn't work my question specifically enough however. I was actually looking
for a way to 'ALTER' the table so that my State field data is always upper.
In MSSQL you can apply a function to a field, which will then run that
After I upgraded from 4.0.16 to 4.1.11 I have found that the date fields are
being pre-formatted and are causing problems to back-ground programs. Is
there a configuration or session setting that will force the date formatting
to the standard in 4.0 and earlier to correct the program issues?
>Although correct, many people consider this bad style - the ON
clause
>of the JOIN should contain only the join condition(s). So it would
be
>better to say
Yes indeed but here the 'zip' condition is in the join for the
possibility that the constant zip condition could speed up the join.
See S
Hello,
we are working on a project that will be using multiple languages to be
displayed and stored in a database, for producing various text strings in
english and the language associted with it's english counter part.
What do we need to consider before creating the database and tables that wil
Hello,
we are working on a project that will be using multiple languages to be
displayed and stored in a database, for producing various text strings in
english and the language associted with it's english counter part.
What do we need to consider before creating the database and tables that wil
So you want 5 contacts for every user? Try this:
SELECT COUNT(c.id) AS count, u.username, u.first_name, u.last_name,
c.name
FROM user AS u, contact AS c
WHERE u.id = c.id_user
GROUP BY c.id_user
HAVING count <= 5
Jerry Swanson wrote:
>How to select 5 records(including username, first_name, l
I need 5 records per user.
On 5/11/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi,
> try this,
>
> select username, first_name,last_name, name as contact
> from user,contact
> where user.id=contact.user_id
> order by username;
>
> Mathias
>
> Selon Jerry Swanson <[EMAIL PROTECTED]>:
>
>
Hi,
try this,
select username, first_name,last_name, name as contact
from user,contact
where user.id=contact.user_id
order by username;
Mathias
Selon Jerry Swanson <[EMAIL PROTECTED]>:
> How to select 5 records(including username, first_name, last_name,
> contact) for each user in the database
Hello.
I see nothing wrong in your settings. The problem could be that
special characters in windows have different codes than in latin1,
as was mentioned by Dusan Pavlica. When you'll debug this issue
try to keep the table's and column's encoding the same as in SET NAMES,
you could lose som
Hello.
Use 5.0.4. Please, could you send an example of query (with pattern)
for words with sensitive characters which worked in 5.0.2 and doesn't
work in 5.0.4?
Scott Klarenbach <[EMAIL PROTECTED]> wrote:
> I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in
> one of my
How to select 5 records(including username, first_name, last_name,
contact) for each user in the database?
table user(
id,
username,
first_name,
last_name
)
contact(
id
id_user //id from user table
name
)
--
MySQL General Mailing List
For
From: "Steve Buehler"
> I am trying to figure something out about the compress function. If I
> wanted to compress a field in a column/row and write it back to the table,
> how can I do this? Is it possible to do it in one command? I tried:
> update `conflicts` set `fname`=(SELECT compress(`fnam
Steve Buehler <[EMAIL PROTECTED]> wrote on 05/11/2005 09:36:44 AM:
>I am trying to figure something out about the compress function. If
I
> wanted to compress a field in a column/row and write it back to the
table,
> how can I do this? Is it possible to do it in one command? I tried:
> u
Andrew Braithwaite wrote:
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200',
news <[EMAIL PROTECTED]> wrote on 05/11/2005 09:09:36 AM:
> In article <[EMAIL PROTECTED]>,
> Peter Brawley <[EMAIL PROTECTED]> writes:
>
> > Scott, sorry, my mistake,
> > SELECT price
> > FROM fedex_zones z
> > INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
> > WHERE r.weight
I am trying to figure something out about the compress function. If I
wanted to compress a field in a column/row and write it back to the table,
how can I do this? Is it possible to do it in one command? I tried:
update `conflicts` set `fname`=(SELECT compress(`fname`) FROM `conflicts`
WHERE
In article <[EMAIL PROTECTED]>,
Peter Brawley <[EMAIL PROTECTED]> writes:
> Scott, sorry, my mistake,
> SELECT price
> FROM fedex_zones z
> INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
> WHERE r.weight = 25;
> PB
Although correct, many people consider this bad style - the ON
Selon Jay Blanchard <[EMAIL PROTECTED]>:
> [snip]
> I've got a converted from Excel spreadsheet to mysql database, which has
> mixed case column names and
> also columns beginning with a digit eg 01_name, 02_address etc what
> upsets PHP considerably.
>
> So I'd like to have a way to generically r
Andrew,
I think you'll get what you want if you add "order by fieldname desc" on the
end of your query, but that's only because the order you have specified
happens to be in reverse ascii order.
Andy
> -Original Message-
> From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]
> Sent: 11 May
Hi !
First, could do avoir hijacking somebody else thread ?
Andrew Braithwaite wrote:
Hello,
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041
[snip]
I've got a converted from Excel spreadsheet to mysql database, which has
mixed case column names and
also columns beginning with a digit eg 01_name, 02_address etc what
upsets PHP considerably.
So I'd like to have a way to generically rename all columns beginning
with a numeric
form ^\d.*
Hi!
What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one server to another and I need to
rebuild the links between tables with new id's on the new server (I'm using
auto_increment extensively), and I have a solution that involves many u
Hello,
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026');
Hi Shaun,
I guess you could try something like:
UPDATE table SET col = RIGHT(col, LENGTH(col) - 3 ) WHERE ...
/Johan
shaun thornburgh wrote:
Hi,
is it possible to alter fields in a column by removing the first three
characters?
i.e. change 100123456789 to 123456789
Thanks for your help
--
MySQ
[snip]
Thanks for your reply but I think you are missing my point or I am
missing yours either way the query does not work when I substitute the
S for a 0 in the part number as I described previously in detail. You
focus on 10902 which does work whether I put single quotes around same
or not
Look at the substring example here
http://dev.mysql.com/doc/mysql/en/string-functions.html
Regards,
Reinhart Viane
-Oorspronkelijk bericht-
Van: shaun thornburgh [mailto:[EMAIL PROTECTED]
Verzonden: woensdag 11 mei 2005 13:30
Aan: mysql@lists.mysql.com
Onderwerp: Remove 1st 3 Chars
Hi,
Hi Ezequiel,
see:
http://dev.mysql.com/doc/
/Johan
Ezequiel Rodriguez wrote:
well im from argentina, and today i begin using mysql, i have a lot of
questions, first of all, is there a mysql official manual at www.mysql.com? i
have searched but don't found it :S
I want to read something before ask
Hi,
is it possible to alter fields in a column by removing the first three
characters?
i.e. change 100123456789 to 123456789
Thanks for your help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
well im from argentina, and today i begin using mysql, i have a lot of
questions, first of all, is there a mysql official manual at www.mysql.com? i
have searched but don't found it :S
I want to read something before ask thnx :D
--
MySQL General Mailing List
For list archives: http://lists.mys
Dave Shariff Yadallee wrote:
I am trying to set up a chem structures table.
column 1 is the key, column 2 is the description, column 3 is the structure
which is a blob.
In PHP the first 2 columns comes out as text as expected, but the
3rd I am trying to tell PHP please ignore this initially since
t
Paul Halliday wrote:
srcaddr VARCHAR(15),
dstaddr VARCHAR(15),
Are these ip-adresses? If they are, consider using UNSIGNED INT columns
and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of
space, thus increase the amount of data your hw can handle.
They are indeed i
On Wed, 11 May 2005 12:22:21 +0200, wrote:
>Hi,
>this is the idea,
>
>mysql> create table t (01_t varchar(10));
>ERROR 1046 (3D000): No database selected
>mysql>
>mysql> use world
>Database changed
>mysql> create table t (01_t varchar(10));
>Query OK, 0 rows affected (0.20 sec)
>
>mysql>
>mysql>
Hi,
this is the idea,
mysql> create table t (01_t varchar(10));
ERROR 1046 (3D000): No database selected
mysql>
mysql> use world
Database changed
mysql> create table t (01_t varchar(10));
Query OK, 0 rows affected (0.20 sec)
mysql>
mysql>
mysql> desc t
-> ;
+---+-+--+-
On Mon, 09 May 2005 17:32:29 +0100, wrote:
>Hi,
>Is there a query which will "alter" a table to rename all the columns to
>lowercase
>
>eg Price to price, Quantity to quantity
Funny how sometimes a UseNet query is answered in minutes, and or exites a
flurry of responses, and
othertimes like h
Scott, sorry, my mistake,
SELECT price
FROM fedex_zones z
INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
WHERE r.weight = 25;
PB
Scott Haneda wrote:
on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote:
Scott,
...In part, my t
78 matches
Mail list logo
