two-column indexes and joins with ranges
I have a performance/index usage problem, and I am hoping somebody can tell me why this problem exists, and whether there is a better solution than the workaround that I'm using now. The problem: I have a table with a two column index, such as CREATE TABLE transactions ( account_id INT NOT NULL, when DATETIME NOT NULL, INDEX(account_id, when) ); I frequently need to do selections like: SELECT * FROM accounts, transactions WHERE accounts.name = 'dave' AND accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10'; If dave has a huge number of transactions (and some of the accounts do), this runs very slowly. EXPLAIN tells me that only the account_id part of the transactions index is being used, with the ref index usage type - apparently, every single one of dave's transactions is being examined, and the when BETWEEN ... part of the join is only being looked at after fetching the rows, even though the index itself had all the data needed! If I do this query in two parts, like this: SELECT account_id FROM accounts WHERE name = 'dave'; SELECT * FROM transactions WHERE account_id = dave's account ID AND when BETWEEN '2004-02-05' AND '2004-02-10'; Suddenly the performance is great, and the full index is used, with a range type of index usage. The MySQL documentation says that the range type can only be used on constants - but I'm wondering, why does it count this as non-constant? The dates I'm using are constant, so it seems that it should be very easy for mysql to do it fast all in one query. Only the first column of the index is variable. I thought maybe the range index type didn't work when there could be multiple hits on the first half of the index, but SELECT * FROM transactions WHERE account_id IN (3, 10, 50) AND when BETWEEN ... also uses the range index type and runs very fast! So, can anybody mysql can't do the right thing with the single-statement query? And is there a better option for me than to do it in two queries? I'm using MySql 4.0.x, I was curious about whether using subqueries in 4.1.x would help, but the point is moot right now since my production systems won't be using 4.1.x until it leaves gamma testing. Bill Shubert ([EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
Re: compare schemas
Josh Howe wrote: Does anybody know of any free tools to compare two mysql schemas? Thanks. you still have the quick solution of : mysqldump --no-data db1 db1.sql mysqldump --no-data db2 db2.sql diff -u db1.sql db2.sql -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: views/cursors in mysql 4.0
Yes. Best of luck with your conversion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 05:25:22 PM: Hi, Thank you very much for your reply. So for the cursors result set C API's will be suitable ..right?. thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: the table is read only
Hi, I just encountered a similar problem on one of my production servers this morning. (I'm still investigating the cause.) After doing a quick bit of Google-searching, this solved my problem: mysqladmin -u username -p flush-tables By the way: All directories in /var/lib/mysql should have 700 permissions (owned my the mysql user) and everything within those directories should be 660 (owned by the mysql user and mysql group). (This was on a FreeBSD 4.8 server running MySQL Server 3.23.58) Hope this helps, Seth On Wed, 22 Sep 2004 08:36:21 +0800, [EMAIL PROTECTED] wrote: Hi I have installed mysql some software on aix5.2 . the edition is MySQL-3.23.58-2.aix5.1.ppc but when I used phpmyadmin to manage the mysql it told me ** #1036 - Table 'gbook' is read only *** while I have granted the data dir(/var/lib/mysql) 777 permission how to resolve it ? help me please ,how to resolve thanks a lot Yours LiRui [EMAIL PROTECTED] 2004-09-20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-column indexes and joins with ranges
Well, you haven't posted the output of EXPLAIN, but I'll take a guess. I expect mysql sees your query as having a JOIN condition of accounts.account_id = transactions.account_id and two WHERE conditions: WHERE accounts.name = 'dave' AND when BETWEEN '2004-02-05' AND '2004-02-10' The optimizer first considers the 2 WHERE conditions, looking for the one it believes will result in fewer rows. Presumably there is an index on accounts.name, but there is no usable index on `when`, as it doesn't come first in the multi-column index. Even if there were a usable index on `when`, I expect there would be fewer rows in accounts with the correct name than rows in transactions within the date range. So, the optimizer chooses accounts as the first table. For each row found in accounts with the right name, it matches that up with rows in transactions according to the JOIN condition, using the first part of the index. Finally, it applies the remaining WHERE condition on the results to filter the matching rows. This may be a case where Shawn's usual advice is the way to go. That is, change your join to an explicit join rather than an implicit join, and move all the relevant conditions to the ON clause. In other words, see if SELECT * FROM accounts JOIN transactions ON accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10' WHERE accounts.name = 'dave'; does any better. Michael William M. Shubert wrote: I have a performance/index usage problem, and I am hoping somebody can tell me why this problem exists, and whether there is a better solution than the workaround that I'm using now. The problem: I have a table with a two column index, such as CREATE TABLE transactions ( account_id INT NOT NULL, when DATETIME NOT NULL, INDEX(account_id, when) ); I frequently need to do selections like: SELECT * FROM accounts, transactions WHERE accounts.name = 'dave' AND accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10'; If dave has a huge number of transactions (and some of the accounts do), this runs very slowly. EXPLAIN tells me that only the account_id part of the transactions index is being used, with the ref index usage type - apparently, every single one of dave's transactions is being examined, and the when BETWEEN ... part of the join is only being looked at after fetching the rows, even though the index itself had all the data needed! If I do this query in two parts, like this: SELECT account_id FROM accounts WHERE name = 'dave'; SELECT * FROM transactions WHERE account_id = dave's account ID AND when BETWEEN '2004-02-05' AND '2004-02-10'; Suddenly the performance is great, and the full index is used, with a range type of index usage. The MySQL documentation says that the range type can only be used on constants - but I'm wondering, why does it count this as non-constant? The dates I'm using are constant, so it seems that it should be very easy for mysql to do it fast all in one query. Only the first column of the index is variable. I thought maybe the range index type didn't work when there could be multiple hits on the first half of the index, but SELECT * FROM transactions WHERE account_id IN (3, 10, 50) AND when BETWEEN ... also uses the range index type and runs very fast! So, can anybody mysql can't do the right thing with the single-statement query? And is there a better option for me than to do it in two queries? I'm using MySql 4.0.x, I was curious about whether using subqueries in 4.1.x would help, but the point is moot right now since my production systems won't be using 4.1.x until it leaves gamma testing. Bill Shubert ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
Have you considered replication http://dev.mysql.com/doc/mysql/en/Replication.html? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
matching people with projects via resources
I'm having difficulty constructing a query. I've got two kinds of information:
a table of resources that various people have, and a table of resources that
various projects need.
===
CREATE TABLE `people` (
`name` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint');
CREATE TABLE `project` (
`proj` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===
I need a query that will tell me which people have the resources required to
complete a given project. Unfortunately all I can get are incomplete matches:
I'm not sure how to express the concept of fully satisfying the requirements
to MySQL.
Restructuring the tables is allowed: I'm not tied to the current schema, I just
need to solve the problem. The only limit is that resources must be arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just matching resource
to resource without excluding Lincoln from building an Ark (no canvas).
mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark | wood| noah|
| ark | wood| lincoln |
| ark | canvas | noah|
| ark | canvas | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas | noah|
| monalisa | canvas | davinci |
| monalisa | paint | davinci |
| jeans| canvas | noah|
| jeans| canvas | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+
It would also be sufficient but less optimal to solve a subset of this problem,
where I only determine who could complete this project for a single project
rather than trying to match all projects to all people in one query.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** matching people with projects via resources
[snip] I'm pretty sure this is a good starting point, but that's just matching resource to resource without excluding Lincoln from building an Ark (no canvas). mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN people ON project.rsrc=people.rsrc; +--+-+-+ | proj | rsrc| name| +--+-+-+ | ark | wood| noah| | ark | wood| lincoln | | ark | canvas | noah| | ark | canvas | davinci | | cabin| wood| noah| | cabin| wood| lincoln | | monalisa | canvas | noah| | monalisa | canvas | davinci | | monalisa | paint | davinci | | jeans| canvas | noah| | jeans| canvas | davinci | | jeans| sewingmachi | NULL| +--+-+-+ [/snip] You haven't stated the resources required for the project in the query. Try this ... SELECT project.proj, project.rsrc, people.name FROM project LEFT JOIN people ON project.rsrc=people.rsrc WHERE project.rsrc = 'wood' AND project.rsrc = 'canvas' Not tested, but limits people to thos who have only all of the resources. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
yeah, I seen that have. Have you tried that before? I need to do it two-way. But have not seen any special setup for that. Each site will be entering data and all sites will need to see the updated change. On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen [EMAIL PROTECTED] wrote: Have you considered replication http://dev.mysql.com/doc/mysql/en/Replication.html? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: matching people with projects via resources
Hi Laszlo,
This is sort of a butchery, and might be a little nicer with two queries
and a temp table, but this works in mysql 4.1.3-beta (at least, it did
for me).
SELECT A.name, B.proj
FROM people as A, project as B
WHERE A.rsrc=B.rsrc
GROUP BY A.name, B.proj
HAVING COUNT(*)=(SELECT COUNT(*) FROM project WHERE proj=B.proj);
This counts up the number of rows each (name,project) pair with
resources in common and then checks to see if it's equal to the total
number of resources for that project. This would be pretty slow on a
really huge table (in the tens of thousands, maybe? I don't have a
great sense for how it would scale), in which case you'd want to make a
temporary table with all of the resource counts cached per project.
Hope that helps,
Matt
Laszlo Thoth wrote:
I'm having difficulty constructing a query. I've got two kinds of information:
a table of resources that various people have, and a table of resources that
various projects need.
===
CREATE TABLE `people` (
`name` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint');
CREATE TABLE `project` (
`proj` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===
I need a query that will tell me which people have the resources required to
complete a given project. Unfortunately all I can get are incomplete matches:
I'm not sure how to express the concept of fully satisfying the requirements
to MySQL.
Restructuring the tables is allowed: I'm not tied to the current schema, I just
need to solve the problem. The only limit is that resources must be arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just matching resource
to resource without excluding Lincoln from building an Ark (no canvas).
mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark | wood| noah|
| ark | wood| lincoln |
| ark | canvas | noah|
| ark | canvas | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas | noah|
| monalisa | canvas | davinci |
| monalisa | paint | davinci |
| jeans| canvas | noah|
| jeans| canvas | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+
It would also be sufficient but less optimal to solve a subset of this problem,
where I only determine who could complete this project for a single project
rather than trying to match all projects to all people in one query.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** RE: **[SPAM]** matching people with projects via resources
[snip] You haven't stated the resources required for the project in the query. Try this ... SELECT project.proj, project.rsrc, people.name FROM project LEFT JOIN people ON project.rsrc=people.rsrc WHERE project.rsrc = 'wood' AND project.rsrc = 'canvas' Not tested, but limits people to thos who have only all of the resources. [/snip] Will not workmany-to-many relationship... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
please help me!
Hi, I am trying to install MySQL without any joy. I would appreciate if you could help me please. Here I have listed the steps I took it to install. 1. I double click on INSTALL-BINARY in mysql.docs 2. Got the message as Windows cannot open this fiel: File: INSTALL-BINARY To open this file, Windows need to know what program created it. Because I do not know the program therefore I used an option as use the Web services to find the appropriate program and I am getting the following error message: Bad Request (Invalid URL) From _http://shell.windows.com/fileassoc/0409/xml/redir.asp?Ext_ (http://shell.windows.com/fileassoc/0409/xml/redir.asp?Ext) =A Your help to install MySql will be highly appreciateed Many Thanks Abdul syed
memory utilization
Hi folks, I have to setup some high performance servers that will be used for MySQL databases and have a couple questions regarding MySQL running on Linux AMD-64 (Opteron). We are looking at setting up these machines with 16-64 gb of RAM, can MySQL running on Linux handle this amount of RAM efficiently? Also most of the tables (almost all of them) will be using the InnoDB storage engine, any pointers on what configuration settings we should use? (for example on a 16 gb RAM server) Anyone have experience with this kind of setup? Regards, Mark Steele Implementation Director CDT Inc. Tel: (514) 842-7054 Fax: (514) 221-3395 smime.p7s Description: S/MIME cryptographic signature
Re: Sync db
Two way replication is possible. However, it does not work like you think it would. It would not be safe to modify data on both databases at the same time. See the FAQ on replication: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html There is some information regarding two-way replication that will shed some light on the subject. Jim Grill yeah, I seen that have. Have you tried that before? I need to do it two-way. But have not seen any special setup for that. Each site will be entering data and all sites will need to see the updated change. On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen [EMAIL PROTECTED] wrote: Have you considered replication http://dev.mysql.com/doc/mysql/en/Replication.html? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- 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]
CONVERT CHARACTER SET option work on TEXT fields?
Folks -- I'm using Client version:mysql Ver 14.6 Distrib 4.1.5-gamma, for pc-linux (i686) Server version: 4.1.5-gamma-standard-log They both happen to both be on the same system, which is RHEL AS 3. All defaults are now UTF8: Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 I'm trying to convert a table (from a database dumped from a 4.0.x server) to UTF8 on this other (4.1.5) server. Following the instructions on http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html, From MySQL 4.1.2 on, if you want to change all character columns (|CHAR|, |VARCHAR|, |TEXT|) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. See section 11.10 Upgrading Character Sets from MySQL 4.0 http://dev.mysql.com/doc/mysql/en/Charset-upgrading.html. I ran the following: ALTER TABLE EJOURNAL CONVERT TO CHARACTER SET utf8; ALTER TABLE EJOURNAL DEFAULT CHARACTER SET utf8; ALTER DATABASE ERESDB DEFAULT CHARACTER SET utf8; This was successful on the varchar(255) field, which went from: Acta cir?rgica brasileira to Acta cirúrgica brasileira Unfortunately, it was not successful on the *text* field, which has remained: | Acta cir?rgica brasileira| | Again, the quoted documentation above *explicitly* says that running the ALTER TABLE command with CONVERT TO CHARACTER SET option will change *TEXT* columns. That has not been my experience. Anyone tell me what I'm doing wrong here? TIA, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
It's safe to implement two-way replication (properly called multimaster replication) in MySQL if each master is guaranteed to only update a unique subset of that data (ie data that no other master database changes). Each master database would be able to safely read all the data, however. For example, say you have a database that exists on two servers, with replicated data consisting of one table and four records, which I'll call A, B, C and D. If server 1 only ever updates records A and B, and server 2 only ever updates C and D, then there is no issue with multimaster replication. The issue is conflict resolution. In the example above, say record A was updated on server 1 at the same time record A was updated on server 2. Which change is correct? What if the conflict was not so trivial, but involved records on multiple tables with auto-incremented primary keys and foreign key constraints to other records inserted or updated at the same time? You could end up with a real mess. Some other database vendors do offer multimaster replication (Oracle being the big one), but I've heard that even their conflict resolution algorithms are not perfect. In your case, you need to ensure that the same record is not being updated (or deleted) on one database at the same time it is being accessed on another database. You have to figure out how to do that yourself, and implement it in whatever application is talking to the database(s). MySQL can't do it for you, at least not right now. Have you considered having read-only databases at the remote locations, and a single master that people write to which in turn sends changes to the 4 read-only databases? David Jim Grill wrote: Two way replication is possible. However, it does not work like you think it would. It would not be safe to modify data on both databases at the same time. See the FAQ on replication: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html There is some information regarding two-way replication that will shed some light on the subject. Jim Grill yeah, I seen that have. Have you tried that before? I need to do it two-way. But have not seen any special setup for that. Each site will be entering data and all sites will need to see the updated change. On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen [EMAIL PROTECTED] wrote: Have you considered replication http://dev.mysql.com/doc/mysql/en/Replication.html? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- 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: matching people with projects via resources
This may not be elegant, but why not define a 3rd table proj_c containing
proj and project_rsrc. This assumes that when you define a project you know
how many resources are required.
CREATE TABLE proj_c (
proj varchar(11) default NULL,
project_rsrc INT default 0);
INSERT INTO proj_c
VALUES
('ark',2),('cabin',1),('monalisa',2),('jeans',2);
Then the sql becomes
mysql SELECT name, count(people.rsrc) AS person_rsrc, project_rsrc,
project.proj
- FROM people
-LEFT JOIN project
-USING (rsrc)
-LEFT JOIN proj_c
-ON (project.proj = proj_c.proj)
- GROUP BY name, project.proj
- HAVING person_rsrc = project_rsrc
- ;
+-+-+--+--+
| name| person_rsrc | project_rsrc | proj |
+-+-+--+--+
| davinci | 2 |2 | monalisa |
| lincoln | 1 |1 | cabin|
| noah| 2 |2 | ark |
| noah| 1 |1 | cabin|
+-+-+--+--+
4 rows in set (0.00 sec)
-Original Message-
From: Laszlo Thoth [mailto:[EMAIL PROTECTED]
Sent: Friday, October 01, 2004 10:53 AM
To: [EMAIL PROTECTED]
Subject: matching people with projects via resources
I'm having difficulty constructing a query. I've got two kinds of
information:
a table of resources that various people have, and a table of resources that
various projects need.
===
CREATE TABLE `people` (
`name` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('
davinci','paint');
CREATE TABLE `project` (
`proj` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('mon
alisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===
I need a query that will tell me which people have the resources required to
complete a given project. Unfortunately all I can get are incomplete
matches:
I'm not sure how to express the concept of fully satisfying the
requirements
to MySQL.
Restructuring the tables is allowed: I'm not tied to the current schema, I
just
need to solve the problem. The only limit is that resources must be
arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just matching
resource
to resource without excluding Lincoln from building an Ark (no canvas).
mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark | wood| noah|
| ark | wood| lincoln |
| ark | canvas | noah|
| ark | canvas | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas | noah|
| monalisa | canvas | davinci |
| monalisa | paint | davinci |
| jeans| canvas | noah|
| jeans| canvas | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+
It would also be sufficient but less optimal to solve a subset of this
problem,
where I only determine who could complete this project for a single
project
rather than trying to match all projects to all people in one query.
--
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: two-column indexes and joins with ranges
Thanks for the suggestion, but no luck. Here's the explain output for, in order, my original query, the 2-part query, and the explicit join (note, not quite like my first post, I had cleaned up my tables to simplify the situation): mysql explain SELECT * FROM accounts, transactions WHERE canon_name = 'wms' AND state = 'active' AND id = account_id AND date_stamp BETWEEN 109658881 AND 109926721; +--+--+---++-+-+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--+-+ | accounts | ref | PRIMARY,canon_name| canon_name | 11 | const,const | 1 | Using where | | transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id | 35 | Using where | +--+--+---++-+-+--+-+ 2 rows in set (0.01 sec) The account_id key is a 2 column key, date_stamp is an 8 byte int (java-style date stamp), so you can see only the 3-byte account_id part of the key is used. If I look up the wms account ID and plug that in directly, basically doing two seperate selects to get the data I want, I get: mysql explain SELECT * FROM transactions WHERE account_id = 45 AND date_stamp BETWEEN 109658881 AND 109926721; +--+---+---++-+--+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+---+---++-+--+--+-+ | transactions | range | date_stamp,account_id | account_id | 11 | NULL |1 | Using where | +--+---+---++-+--+--+-+ 1 row in set (0.02 sec) Now you can see that the whole index (all 11 bytes) is being used, with a range type, so this is fast. I tried using a join as you recommended, it gave me: mysql EXPLAIN SELECT * FROM accounts JOIN transactions ON account_id = id AND date_stamp BETWEEN 109658881 AND 109926721 WHERE canon_name = 'wms' AND state = 'active'; +--+--+---++-+-+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--+-+ | accounts | ref | PRIMARY,canon_name| canon_name | 11 | const,const | 1 | Using where | | transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id | 35 | Using where | +--+--+---++-+-+--+-+ 2 rows in set (0.03 sec) So, same as the first case - it is just refusing to use a range type of index, even though in some cases this would reduce the number of rows that must be fetched by a factor of 10 or more. On Fri, 2004-10-01 at 07:36, Michael Stassen wrote: Well, you haven't posted the output of EXPLAIN, but I'll take a guess. I expect mysql sees your query as having a JOIN condition of accounts.account_id = transactions.account_id and two WHERE conditions: WHERE accounts.name = 'dave' AND when BETWEEN '2004-02-05' AND '2004-02-10' The optimizer first considers the 2 WHERE conditions, looking for the one it believes will result in fewer rows. Presumably there is an index on accounts.name, but there is no usable index on `when`, as it doesn't come first in the multi-column index. Even if there were a usable index on `when`, I expect there would be fewer rows in accounts with the correct name than rows in transactions within the date range. So, the optimizer chooses accounts as the first table. For each row found in accounts with the right name, it matches that up with rows in transactions according to the JOIN condition, using the first part of the index. Finally, it applies the remaining WHERE condition on the results to filter the matching rows. This may be a case where Shawn's usual advice is the way to go. That is, change your join to an explicit join rather than an implicit join, and move all the relevant conditions to the ON clause. In other words, see if SELECT * FROM accounts JOIN transactions ON accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10' WHERE accounts.name = 'dave'; does any better. Michael Bill Shubert ([EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
Re: memory utilization
We have an Opteron server with 6 gig of RAM. The issue used to be 4 gig - the max amount of memory a 32-bit processor could access. With 64-bit processors, the amount of accessible memory has jumped into the terrabyte range. Pick a distribution that is for the AMD-64 (we use SuSE 8 Enterprise) and use the 64-bit binary. We used the hints inside the my.cnf for huge databases. You need to apply that formula that you can find in the InnoDB section of the MySQL documentation: Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB Because you are using InnoDB, you can set your key_buffer_size fairly low. On our machine with 6-gig, we have the following settings: sort_buffer_size = 512K read_buffer_size = 512K max_connections = 1400 innodb_buffer_pool_size = 3G innodb_additional_mem_pool_size = 20M key_buffer = 16M To apply the formula to our server, we get 3000 meg + 16 meg + (1400 * (.5 meg + .5 meg)) + 2800 meg Which is about 7.2 gig of memory that might be used by MySQL in a worst case scenario (with all 1400 connections open). We never expect to hit 1400 connections, but we wanted to set it too high at first, and then shrink it down slowly. MySQL is currently using about 3.1 gig at 168 queries per second, with 95 to 98 percent reads. Most of our database is in RAM at any given time. MySQL is using about 5 percent of the two CPUs under this configuration. On our busiest day, when our load is 30% higher, MySQL uses about 10% of the CPU cycles. You should have no problem throwing more RAM into an Opteron. One thing to note, we had to compile our own MySQL - we were getting segfaults with the default binary (something to do with fpic, I believe - I didn't do the actual compilation). Hope that helps. David Mark Steele wrote: Hi folks, I have to setup some high performance servers that will be used for MySQL databases and have a couple questions regarding MySQL running on Linux AMD-64 (Opteron). We are looking at setting up these machines with 16-64 gb of RAM, can MySQL running on Linux handle this amount of RAM efficiently? Also most of the tables (almost all of them) will be using the InnoDB storage engine, any pointers on what configuration settings we should use? (for example on a 16 gb RAM server) Anyone have experience with this kind of setup? Regards, Mark Steele Implementation Director CDT Inc. Tel: (514) 842-7054 Fax: (514) 221-3395 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search question
Laura did this work... inquiring minds want to know :)
On Wed, 29 Sep 2004 13:36:40 -0400, Wesley Furgiuele
[EMAIL PROTECTED] wrote:
Laura:
Perhaps the - is acting like a Boolean operator. What if you put
double quotes around your search phrase:
SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN
BOOLEAN MODE );
Wes
On Wed, 29 Sep 2004 13:22:54 -0400, Laura Scott [EMAIL PROTECTED] wrote:
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: matching people with projects via resources
It's not as elegant as I wanted I got it to work this way (I must be
getting tired)
create temporary table projects_need
SELECT proj
, count(rsrc) as rsrc_count
FROM project
group by proj;
CREATE temporary table suppliers_match
select p.proj
, s.name
, count(s.name) as sup_count
from project p
left join people s /* s for supplier */
on s.rsrc = p.rsrc
group by 1,2;
select pn.proj
, sm.name
, pn.rsrc_count
, sm.sup_count
, sm.sup_count/pn.rsrc_count * 100.0 pct_match
from projects_need pn
inner join suppliers_match sm
on sm.proj = pn.proj
order by proj
, pct_match desc;
drop temporary table projects_need, suppliers_match;
+--+-++---+---+
| proj | name| rsrc_count | sup_count | pct_match |
+--+-++---+---+
| ark | noah| 2 | 2 |100.00 |
| ark | davinci | 2 | 1 | 50.00 |
| ark | lincoln | 2 | 1 | 50.00 |
| cabin| lincoln | 1 | 1 |100.00 |
| cabin| noah| 1 | 1 |100.00 |
| jeans| davinci | 2 | 1 | 50.00 |
| jeans| noah| 2 | 1 | 50.00 |
| jeans| NULL| 2 | 0 | 0.00 |
| monalisa | davinci | 2 | 2 |100.00 |
| monalisa | noah| 2 | 1 | 50.00 |
+--+-++---+---+
You can change the condition of the last query to join on
rsrc_count=sup_count so that you only get full supply list matches but I
thought that having a completion % was an interesting by-product of my
method.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Laszlo Thoth [EMAIL PROTECTED] wrote on 10/01/2004 11:53:23 AM:
I'm having difficulty constructing a query. I've got two kinds of
information:
a table of resources that various people have, and a table of resources
that
various projects need.
===
CREATE TABLE `people` (
`name` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),
('davinci','canvas'),('davinci','paint');
CREATE TABLE `project` (
`proj` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),
('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),
('jeans','sewingmachine');
===
I need a query that will tell me which people have the resources
required to
complete a given project. Unfortunately all I can get are incomplete
matches:
I'm not sure how to express the concept of fully satisfying the
requirements
to MySQL.
Restructuring the tables is allowed: I'm not tied to the current
schema, I just
need to solve the problem. The only limit is that resources must
bearbitrary:
I can't use a SET to define resources because I might want to insert a
new
resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just
matching resource
to resource without excluding Lincoln from building an Ark (no canvas).
mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT
JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark | wood| noah|
| ark | wood| lincoln |
| ark | canvas | noah|
| ark | canvas | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas | noah|
| monalisa | canvas | davinci |
| monalisa | paint | davinci |
| jeans| canvas | noah|
| jeans| canvas | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+
It would also be sufficient but less optimal to solve a subset of
this problem,
where I only determine who could complete this project for a single
project
rather than trying to match all projects to all people in one query.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg Backup
Thank you very much. One more question i.e Is there any command to take the Backup in MySQL 4.0 (windows) classic which does not support innodb. I know 2 commands like mysqldump and mysqlhotcopy. mysqlhotcopy seems to support myism but only in linux and netware os. But i need it in windows. Will mysqldump supports the backup in windows operatiog system with out using innodb?. If not any alternatives are there and how to do that?. Please explain me in detail asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 10/1/2004 6:22 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: views/cursors in mysql 4.0 Yes. Best of luck with your conversion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 05:25:22 PM: Hi, Thank you very much for your reply. So for the cursors result set C API's will be suitable ..right?. thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: full text search question
GH schrieb: Laura did this work... inquiring minds want to know :) Laura: Perhaps the - is acting like a Boolean operator. What if you put double quotes around your search phrase: SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN BOOLEAN MODE ); Or.. the - is possibly supposed to be escaped? Let's take a look at the documentation ;) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (if !update then insert) sequence - result Duplicate key :(
Hi, When you use the replace command if the row does not exist it is inserted. MySQL Reference Manual: Section 14.1.6 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted Regards, Laercio. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: quinta-feira, 30 de setembro de 2004 11:39 To: [EMAIL PROTECTED] Subject: Re: (if !update then insert) sequence - result Duplicate key :( In article [EMAIL PROTECTED], Laercio Xisto Braga Cavalcanti [EMAIL PROTECTED] writes: Hi, To solve this you can use the REPLACE command. The problem is that Aleksandr wants to increment a counter, not set it to some fixed value. How could you use REPLACE for that? -- 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: (if !update then insert) sequence - result Duplicate key :(
Laercio Xisto Braga Cavalcanti wrote: When you use the replace command if the row does not exist it is inserted. MySQL Reference Manual: Section 14.1.6 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted Read what you quoted. The old record is *deleted* if it exists, and then a new record is inserted. So he wouldn't be able to get the incremented count. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GINA Development
I am working on a replacement GINA (Graphical Identification and
Authentication) for the University I work for. It is a DLL written in C
and I need to add mysql connectivity to it.
I am writing this in Visual Studio 2003. It took me forever to get all
the MySQL API's libs to link correctly but I finally have a fully
functioning version of a simple program that connects to the database.
I would like all of the connectivity info (user, pass, etc.) to be found
in the my.ini file. According to the API docs this is possible and
simple. But it is not working. I even found an old list item that
gives an example (and it almost matched my code exactly) that is
supposed to work.
Here is my code:
#include stdafx.h
#include stdio.h
#include stdlib.h
#include windows.h
int _tmain(int argc, _TCHAR* argv[])
{
MYSQL mysql;
mysql_init(mysql);
mysql_options(mysql,MYSQL_READ_DEFAULT_FILE,./my.ini);
mysql_options(mysql,MYSQL_READ_DEFAULT_GROUP,MYSQL-API);
if (!mysql_real_connect(mysql,NULL, NULL, NULL, NULL, 0, NULL,
0))//mysql,chilton.byu.edu,liil,itsgr8$you,machine,0,NULL,0))
{
fprintf(stderr, \nFailed to connect to database: Error:
%s\n\n,
mysql_error(mysql));
}
else printf(\nConnected to database!!\n\n);
mysql_close(mysql);
return 0;
}
And here is my.ini
[MYSQL-API]
user='user'
password='**'
Server='chilton.***.edu'
database='machine'
the * are there for the purpose of this email only. I have tried the
data with and without the single quotes.
Here is the error I receive :
Failed to connect to database: Error: Can't connect to MySQL server on
'localhost' (10061)
It appears to be trying to connect at least but it is not getting the
user name.
If I replace the 'null' in the code with the actual data it connects
just fine.
According to everything that I have read this should work. Any ideas?
Thanks
Dan
RE: GINA Development
And here is my.ini [MYSQL-API] user='user' password='**' Server='chilton.***.edu' database='machine' the * are there for the purpose of this email only. Even though you put *** in the server address, I bet it's Server='chilton.BYU.edu' Considering that your email comes from a byu.edu address hehe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order rows before applying GOUP BY / DISTINCT
Hello. I'm having a problem where I seem to need to order a table before applying group by (or distinct?) in my query. Here is a simplified table structure example: ID USER HOST TIME ID = Primary Key I would like to do the following in ONE query, if possible: I am looking to retrieve the LAST time 10 UNIQUE users were registered in the table (user+host+time). These users should be the last 10 people to be inserted into the table (each user can appear various times in the table, like in a log). At the same time, I would like to retrieve the TOTAL NUMBER of times each of these users appear in the table, but this is not very important. This was the query I adopted until noticing it had a severe problem: select user, host, time, count(user) as times from userlog where user!='' group by user order by time desc limit 10; The problem is that the TIME associated with each person isn't the LAST TIME a registry was done for the user. This makes me think that I might need to order the TIME column before doing the GROUP BY, but I do no know how (and it might not even be the solution to the problem!). I do not know if I managed to express myself very well, but if anyone is willing to help, I would of course clarify things if necessary. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing for OR clauses
Wondering if anyone can give me advice on indexing for OR clauses. I have a table with a number of fields, two of which are sender_id and receiver_id. I also have a query such as this: SELECT ... WHERE (sender_id = 98765 OR reciever_id = 98765) The query is OK for a limit of 10, but if I increase that to 25, it becomes inordinately slower (it is a large table). Is it better for me to have two separate indexes, one for each of sender_id and receiver_id, or one index with both sender_id and receiver_id? Or should I just avoid the use of OR? Or am I simply missing something? -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing for OR clauses
At 06:03 PM 10/1/2004, you wrote: Wondering if anyone can give me advice on indexing for OR clauses. I have a table with a number of fields, two of which are sender_id and receiver_id. I also have a query such as this: SELECT ... WHERE (sender_id = 98765 OR reciever_id = 98765) The query is OK for a limit of 10, but if I increase that to 25, it becomes inordinately slower (it is a large table). Is it better for me to have two separate indexes, one for each of sender_id and receiver_id, or one index with both sender_id and receiver_id? Or should I just avoid the use of OR? Or am I simply missing something? -Dave Dave, If you do an Explain it will likely show it uses only 1 index. You need to break it into 2 queries and merge them together using Union. This shouldn't take long because it is only returning a few rows. See http://dev.mysql.com/doc/mysql/en/UNION.html. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Visio to diagram MySQL db, export SQL
Is there a version or a product available for LINUX? On Tue, 28 Sep 2004 13:55:19 -0500, Tim Hayes [EMAIL PROTECTED] wrote: ari MYdbAL which you can download at www.it-map.com is completely FREE and includes data modeling, DDL generation or whatever you need to create your MYSQL database. Tim -Original Message- From: Ari Davidow [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 12:59 To: [EMAIL PROTECTED] Subject: Using Visio to diagram MySQL db, export SQL Hi, I have Visio 2002. I am trying to set it up to use MySQL-specific datatypes (e.g., ENUM) and have some success using the User-Defined Types. But what I really want is something that I can export from Visio to actual SQL statements, and I am not succeeding in finding that at all. If Visio is total toast for this purpose, is there a comfortable open source tool that works under windows that will let me/help me visually set up my entity diagrams as I work out my database schema? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.com/ -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php pages not inserting data into table
i'am not sure if this is related to suse 9.1 or what i'am beginning with mysql and php i'am running: mysql 4.0.21-standard suse9.1 kernel version 2.6.5-7.10b-default php version 4.3.4 - default install apache 2.0.49 - prefork i created an address book using php and when i go thru and fill in all the required feilds and click on submit it just resets the form. i was previously using this on mandrake 9.2 and it worked flawlessly however i was using apache 1.3 i fifgured it was something worng in my php scripting but i took a sample calculator from php.net and it it too was not passing data to mysql i have searched google many times over and different forums and never found my answer or i'am going in the wrong direction. i was looking into mysql modules that apache uses but that lead to dead ends. looked into how php handels the data and that too lead me to a dead end. i wiped my machine out and reinstalled the os figured i missed something or there was a corrupt file but that apparently was not the case. if some one could make some helpful suggestions as to whats causing this i would be much appreciative thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
