Basically it says that if you have an index let's say INDEX_1 on columns:
INDEX_1 : a, b, c, d
MySQL will act as if you had setup indexes on:
INDEX_1_1 : a, b, c
INDEX_1_2 : a, b
INDEX_1_1 : a
A query like:
SELECT a FROM table_name WHERE a 9; - will use the index
SELECT a, b, c FROM
Hi all
I'm gradually learning how much simpler it is to do things with joins. I can
tell that I haven't seen the light yet... but I'm expecting fireworks pretty
soon when it all falls into place brain-wise and I can wallow in the joy of
smaller more efficient sql!
I'd really appreciate a
Hello everyone!
I have a problem with matching in grouped rows.
I have:
- one DB with customers
- one DB with advertisement articles
- one DB that holds what customer got which article
the linked DB looks like:
CREATE TABLE adverticlelink (
c_id int(11) NOT NULL,
aa_id int(11) NOT NULL,
Good day list,
I've encountered a behaviour with 'WITH ROLLUP' that I don't understand.
SELECT a.report_date,
SUM(b.daytotal*last1) last1,
( 100*SUM(b.daytotal*last1) /
(select sum(daytotal) FROM aggregate_logs
WHERE aggr_source like 's%' and aggr_date=CURDATE()
)
) tper
FROM
Helen M Hudson ha scritto:
So, if my table structure was:
id | date | order_ref | amount
1 | 1/1/01 | 100 | 1000 these 2 are the rows
2 | 1/1/01 | 100 | 200 i want to exclude
3 | 2/1/01 | 100 | 1000
4 | 2/1/01 | 100 | 200
5 | 2/1/01 | 100 | 50
I'd
Helen M Hudson schrieb:
Hi all
I'm gradually learning how much simpler it is to do things with joins. I can
tell that I haven't seen the light yet... but I'm expecting fireworks pretty
soon when it all falls into place brain-wise and I can wallow in the joy of
smaller more efficient sql!
Yes, I can see how this would work for just the one order and hardcoding the
100... but I cannot assume only to sum distinct values and my table has
other order_refs in it with the same multiple rows of over multiple days, so
I need a more generic select that will list this nice summary for all
Taco Fleur schrieb:
Hi all,
is it possible to do a MATCH AGAINST and return some of the text, for
example the first paragraph that contains the matching words?
Kind regards,
Well you can use the substring function if mysql for that.
Ligaya Turmelle schrieb:
I have been subscribed to this list for a couple of years now. I don't
often respond and rarely ask questions, but I do read it every day and
typically learn something new.
Around the 13th of this month I suddenly stopped receiving the mailing
list (though I was
Alex wrote:
I'm running mysql 5.0.22 on SLES9, using the mysql.com appropriate rpm.
I've tried other versions of mysql 5, including 5.0.6, 5.0.17, 5.0.18
and 5.0.21. The result is always the same. This leads me to believe,
that there are new requirements for mysql 5 and that's the reason
Hi,
2006/6/21, Helen M Hudson [EMAIL PROTECTED]:
Yes, I can see how this would work for just the one order and hardcoding the
100... but I cannot assume only to sum distinct values and my table has
other order_refs in it with the same multiple rows of over multiple days, so
I need a more
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do
On Wednesday 21 June 2006 11:16, Song Ken Vern-E11804 wrote:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select
Hi Gabriel,
Can you tell the benefits of a composite index,
compared to 4 individual indices in this case ?
Suppose I need to select on the fields b, c or d.
Then I also need also indices on fields b, c and d.
Together with the composite index on (a,b,c,d),
there is a lot of redundancy in the
MySQL wil only use one index per table in a query... this is why in
most cases a composite index will do better that a single column
index.
And for the second is true... this is leftmost rule...
You have an index on:
a, b, c
You gain indexes on:
a, b
a
But you will need to set up yourself an
When creating the InnoDB table the InnoDB engine asigns to the FOREIGN
KEY you defined a symbol.
On my server it generated dbmail_messageblks_ibfk_1... and if in the
ALTER statement I entered:
DROP FOREIGN KEY dbmail_messageblks_ibfk_1
Then the ALTER table worked fine...
If you want to
Hey list;
I'm having trouble with the GROUP_CONCAT() function, which (according
to the docs) is supposed to give me a column with the VARCHAR type,
unless group_concat_max_len is 512. Instead it BLOBs me!
Now, I haven't set any value for the group_concat_max_len, so it
shouldn't be different
Hey list;
I'm having trouble with the GROUP_CONCAT() function, which (according
to the docs) is supposed to give me a column with the VARCHAR type,
unless group_concat_max_len is 512. Instead it BLOBs me!
Now, I haven't set any value for the group_concat_max_len, so it
shouldn't be different
Hi all,
I was trying this:
http://forge.mysql.com/snippets/view.php?id=4
That is supposed to emulate a split() in mysql.
Could anyone make it work ?
I've been trying without luck. I 'm getting strange errors when trying to
create this procedure.
_
DROP
Gabriel PREDA wrote:
Hope this helps !
Thanks a bunch, that was it. Problem solved.
I'll tell about it on the dbmail list as well.
Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote:
Hey list;
I'm having trouble with the GROUP_CONCAT() function, which (according
to the docs) is supposed to give me a column with the VARCHAR type,
unless group_concat_max_len is 512. Instead it BLOBs me!
Hmm, I just realized this won't
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote:
Hey list;
I'm having trouble with the GROUP_CONCAT() function, which (according
to the docs) is supposed to give me a column with the VARCHAR type,
unless group_concat_max_len is 512. Instead it BLOBs me!
Hmm, I just realized this won't
Sorry forgot to copy my version:
It's not acepting the delimiter command, can be ? I'm really missing
something here.
+---+
| version() |
+---+
| 4.0.24_Debian-10ubuntu2.3-log |
+---+
mysql
Definitivaly stopping and going for one or two cups of coffee.
I was logged in to another mysql... when I copied the last email. Sorry
guys, I don't want to add extra non-sense text to the list!
+--+
| version()|
+--+
|
Hi,
There are a couple of errors in the way it is defined, firstly
1) There should be a ; after the DROP PROCEDURE statement
2) use a delimiter //, this stops the mysql client trying to interpret
the other ; as the end of the command. It then takes everything until
the next // as belonging to
On Wednesday 21 June 2006 13:37, listsql listsql wrote:
Just for the happy comment: Argentina Will win the match tonight against
Holland :)
MARTIN
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
Perhaps the searches that return nothing are actually matching more than 50% of the record in the table.
From the manual:
In addition, words that are present in more than 50% of the rows are considered
common and do not match.
- Original Message -
From: Taco Fleur [EMAIL PROTECTED]
I am trying to create a trigger which will eventually call a judf.. However
my mysql does not understand the delimiter command. actually... help doesnot
even list delimiter as a possible command.. I am using MySQL- 5.0-22 Max
server ... Installed it from a non-rpm binary distribution.. Also.. is
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id =
Hi,
It's very important to have instaled the library client related to the MySQL
server version.For example, you cannot use libmysql.dll version 3.X or 4.x to
use stored procedure from version 5.X of MySQL RDBMS.
Regards,
_
G.NET SOFTWARE
Is there any way to test this?
I doubt it is 50%, some of these words only appear once or twice within the
content.
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au an industry leader with commercial
IT experience since 1994 .
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-Original Message-
From: Thomas Lundström [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Hi everyone!
I have a bunch of MyISAM tables and one MERGE table. All have same
structure. It seems to me indecies on MERGE table aren't fine. When I
fetch rows by indexed field I get empty result set. Index size showed by
'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange.
Hi all
I have problems getting MySQL autoboot on my RedHat installation. I have
tried to add the mysql.server start script from the install dir to the
system with chkconfig --add mysql (I copied it to /etc/init.d/) and then
trying to add mysql to the default boot order with chkconfig mysql on
but
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
the table, is there a command for this end? (I could copy paste the part
from 'mysqldump' but it's
Hey gang;
If I have understood the boolean search method correctly, from own
experiments and the docs, the asterisk operand cannot be put before a
word - it negates the preceeding word completely. How have you solved
this? I want my searches to match both words that starts with,
contains, and
Hi Eugene,
I suppose you have read:
http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html
Especially the paragraph starting with:
The order of indexes in the MERGE table and its underlying tables should be
the same.
HTH, Cor
- Original Message -
From: Eugene Kosov [EMAIL
On 6/21/06, Fredrik Andersson [EMAIL PROTECTED] wrote:
Hi all
I have problems getting MySQL autoboot on my RedHat installation. I have
tried to add the mysql.server start script from the install dir to the
system with chkconfig --add mysql (I copied it to /etc/init.d/) and then
trying to add
On 6/21/06, Xiaobo Chen [EMAIL PROTECTED] 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
the table, is there a command for this end? (I
You can do something like:
mysqldump --no-data
-Original Message-
From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 9:11 PM
To: mysql@lists.mysql.com
Subject: Just need script for creating tables
Hi, all
If I use 'mysqldump', I will get the script to create
Oops! I think I've missed it...
Thanks a lot! :)
C.R.Vegelin пишет:
Hi Eugene,
I suppose you have read:
http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html
Especially the paragraph starting with:
The order of indexes in the MERGE table and its underlying tables should
be the
--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
Probably a permissions issue. Ensure that the directory in which the
pid file is created (I believe /var/run or /var/lib/mysql on RH) has
write permission for the mysql system user/group. Also, ensure
permissions/ownership on the datadir (/var/lib/mysql) for the mysql
owner/group.
Fredrik
Hello,
I don't understand why user 'test1user' can see database 'test' as well.
I think user 'test1user' should only be able to see database 'test1'.
What did I do wrong here? I'd appreciate any help. Thanks.
As root:
mysql show databases;
+---+
| Database |
Bing Du schrieb:
Hello,
I don't understand why user 'test1user' can see database 'test' as well.
I think user 'test1user' should only be able to see database 'test1'.
What did I do wrong here? I'd appreciate any help. Thanks.
As root:
mysql show databases;
+---+
| Database
Is it possible to get mysqldump to include rights that has been GRANTED to a
database or to tables in the database that is being dumped?
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Hello everyone,
I had a horrible thing happen to me this morning and wanted to make it
known to the community.
I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:
delete from tablename where id - 12345;
Notice
Jørn Dahl-Stamnes schrieb:
Is it possible to get mysqldump to include rights that has been GRANTED to a
database or to tables in the database that is being dumped?
Dump the Grant tables?
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing
Kevin Old schrieb:
Hello everyone,
I had a horrible thing happen to me this morning and wanted to make it
known to the community.
I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:
delete from tablename where id
On Wednesday 21 June 2006 17:17, Barry wrote:
Jørn Dahl-Stamnes schrieb:
Is it possible to get mysqldump to include rights that has been GRANTED
to a database or to tables in the database that is being dumped?
Dump the Grant tables?
I have though of it. Currently I am dumping the mysql
It's not necessarily the word appearing in more than 50% of thre records. If you search result returns more than 50% of the records,
mysql considers the result irrlevant and doesn't return anything.
You can kind of test it by using LIKE.
SELECT count(*) from table where field like %dealer% or
I wouldn't consider that a problem. If you want to search on contains or ends with, you can't use an index. Thus you should use
LIKE.
select * from table where field like %searchtext%
That would give you the functionality you are looking for.
How would you look up a word in the dictionary that
Dear friends
is their any way to optimize this query bellow, it take +- 2minutes do
complete, i think it becouse their no index by the emissao field
SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999
= YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC
luiz Rafael wrote:
Dear friends
is their any way to optimize this query bellow, it take +- 2minutes do
complete, i think it becouse their no index by the emissao field
SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR
(1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`))
database test itself has Grants that it shows itself to everyone.
How should I verify that?
Thanks,
Bing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On 6/21/06, Barry [EMAIL PROTECTED] wrote:
Kevin Old schrieb:
Hello everyone,
I had a horrible thing happen to me this morning and wanted to make it
known to the community.
I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I
The Spanish translation of the MySQL Reference Manual is finally
complete. It was done by one of our partners from Barcelona, Spain, with
a lot of help from our community (volunteer translators and reviewers).
The translation covers MySQL 5.0 and can be found here:
Hi,
On Jun 21, 2006, at 12:24 PM, Kevin Old wrote:
On 6/21/06, Barry [EMAIL PROTECTED] wrote:
Kevin Old schrieb:
Hello everyone,
I had a horrible thing happen to me this morning and wanted to
make it
known to the community.
I needed to delete a record from a very large table (yes, it
Hi Jay
Thanks for the help
Regards
Luiz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dear Friends
how to reclain the disk space used by an table that was dropped?
Regards
Luiz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Luiz, if you are working with MyISAM tables, the table files should be
deleted when you DROP the table. If not, you might have an OS
permissions issue.
If you are working with InnoDB tables in one tablespace, you cannot
currently easily reclaim the space. See
In the last episode (Jun 21), luiz Rafael said:
how to reclain the disk space used by an table that was dropped?
For most storage engines, each table is in its own file so a dropped
table immediately returns space back to the OS. For InnoDB in
tablespace mode (i.e. innodb_file_per_table is
I had to do some disk space recovery mysql last month...
I have backed up some of the raw .MYI, .MYD files and deleted them from
the data directory. Running 5.0.18, I had to shut down mysql and
restart before it freed up the space.
-Original Message-
From: Dan Nelson [mailto:[EMAIL
Jay Pipes wrote:
SELECT * FROM `sav00_sava0400_dbf`
emissao BETWEEN '2000-01-01' AND '2000-12-31'
UNION ALL
SELECT * FROM `sav00_sava0400_dbf`
emissao BETWEEN '1999-12-01' AND '1999-12-31'
Why not:
SELECT * FROM `sav00_sava0400_dbf`
emissao BETWEEN '1999-12-01' AND '2000-12-31'
?? ;)
luiz Rafael wrote:
SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR
(1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC
Are you sure this is what you really want? MONTH() is never greater than
12, so your query is equal to:
SELECT * FROM
On Wed, 21 Jun 2006 11:12:40 -0400, Kevin Old wrote:
Hello everyone,
I had a horrible thing happen to me this morning and wanted to make it
known to the community.
I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this
I need to delete 200,000 rows from an 8 million row MyISAM table using an
index. It's taking 20 minutes which is quite slow. Is there any way to
speed it up? TIA
Mike
MySQL 4.1.10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have the subject book and I want to learn SQL from this book. Alas
the bookbiz.sql file on the cd-rom was last updated in 1996 :-) 10
years ago. I tried to load it on my version 4 mysql and it would not get
far at all. It appears that the software has changed since 1996.
I did the
Is the table heavily indexed? Indexes cause inserts and deletes (and updates
under certain conditions) to slow down.
Also, since you are deleting approx 2.5% of your rows, your delete-statement must have a where-clause. You might want to do an
explain-plan on it to make sure it's not an
Thank you very much for your understandable representation, Mr.PREDA
and Mr.Vegerin.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
At 07:15 PM 6/21/2006, Karl Larsen wrote:
I have the subject book and I want to learn SQL from this book. Alas
the bookbiz.sql file on the cd-rom was last updated in 1996 :-) 10
years ago. I tried to load it on my version 4 mysql and it would not get
far at all. It appears that the
This sounds awfully like doing an rm -rf somefile. * (with an accidental space
in between the . and the *).
Most unix/linux geeks I know (including myself) only ever do this once.
Humans like to learn the hard way, it seems :-)
-- boof
On Thursday 22 June 2006 03:24, Harrison Fisk wrote:
Hi,
On 6/21/06, Jørn Dahl-Stamnes [EMAIL PROTECTED] wrote:
On Wednesday 21 June 2006 17:17, Barry wrote:
Jørn Dahl-Stamnes schrieb:
Is it possible to get mysqldump to include rights that has been GRANTED
to a database or to tables in the database that is being dumped?
Dump the Grant tables?
I'm obviously getting myself into more and more trouble with fulltext..
I have the following SQL statement;
SELECT indexIdentity, webpageIdentity, content, MATCH (content) AGAINST
('gallery' ) as score FROM db_com_pacificmomentum.tbl_index
where MATCH (content) AGAINST ('gallery' );
Which
Is there any way to override this functionality?
It becomes a real pain, I have to start doing weird stuff to overcome this
limitation, if it would just return the results whether its more than 50% or
not, I would be fine. Is there no way to do this? Thanks.
Kind regards,
Taco Fleur
Free
Hello guys... I have stored procedures and I want when a calling program call
my stored procedure it will return a response that the stored procedure
execute successfuly or return an error code to the calling program.
I found a @@session.error_count system variable but if theres an error this
76 matches
Mail list logo
