A Simple Query Help
Dear Friends, I m new on this list, and I m trying to learn more about mysql. After perform a lot of searchs in the Internet, I have no answer to my question and would like to ask your help. I wanna a perform a query that depends of the result from another (query) table inside the same database. On this scenario: I have 02 tables: Table 1 = users Table 2 = sent_emails I wanna select ONLY the users that are NOT inside the table SENT_emails Example: $query1 = SELECT * FROM users WHERE accept_email = 1 The results from query above SHOULD depends of the query Bellow: $query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above AND messageID NOT LIKE = XX The results of the first query, should display only the users that are NOT inside the condition of query 2. I read about INNER JOIN LEFT ... but I can´t understand ... Can help me? With Regards, Rafael Ribeiro
Re: A Simple Query Help
Hi Rafael, You can try using correlated subquery instead of outer join. This can be slow with big tables though: SELECT * FROM users WHERE accept_email = 1 and email not in (SELECT email FROM sent_emails WHERE sent_emails .email = users.email AND messageID NOT LIKE = ‘XX’) OR OUTER JOIN as a better option: SELECT u.* FROM users AS u OUTER LEFT JOIN sent_emails AS se USING (email) where u.accept_email = 1 AND se.messageID NOT LIKE = ‘XX’ AND se.email IS NULL Thanks, Egor SELECT * FROM sent_emails WHERE email in (SELECT email FROM users WHERE accept_email = 1) AND messageID NOT LIKE = ‘XX’ On 04/22/2012 09:30 PM, Rafael Ribeiro wrote: Dear Friends, I m new on this list, and I m trying to learn more about mysql. After perform a lot of searchs in the Internet, I have no answer to my question and would like to ask your help. I wanna a perform a query that depends of the result from another (query) table inside the same database. On this scenario: I have 02 tables: Table 1 = users Table 2 = sent_emails I wanna select ONLY the users that are NOT inside the table SENT_emails Example: $query1 = SELECT * FROM users WHERE accept_email = ‘1’ The results from query above SHOULD depends of the query Bellow: $query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above AND messageID NOT LIKE = ‘XX’ The results of the first query, should display only the users that are NOT inside the condition of query 2. I read about INNER JOIN LEFT ... but I can´t understand ... Can help me? With Regards, Rafael Ribeiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Simple Query Question
On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: Hi, Please help what is wrong with this simple query SELECT COUNT(key_agent) total FROM agents_consolidated WHERE total = 180 Thanks. You need to use having instead of where, see the documentation. Stefan Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Simple Query Question
Hi many thanks for the help :) On Apr 14, 2012, at 6:21 PM, Stefan Kuhn wrote: On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: Hi, Please help what is wrong with this simple query SELECT COUNT(key_agent) total FROM agents_consolidated WHERE total = 180 Thanks. You need to use having instead of where, see the documentation. Stefan Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Fw: Simple Query Question
Hi , count() function is a group function so use group by clause in your select statement if you are selecting more than one column data.. here is some sample query related to help you, select count(*) from trndisburse: output:1467 select count(*),pkdisburseid from trndisburse_TMP m Group by pkdisburseid; output: COUNT(*)PKDISBURSEID 100011120414090807001226 100011120414090807001228 100011120414090807001246 100011120414090807001252 100011120414090807001173 100011120414090807001187 100011120414090807001230 100011120414090807000859 10001112041409080742 10001112041409080751 10001112041409080797 100011120414090807001309 100011120414090807001314 100011120414090807001333 100011120414090807001290 10001112041409 080701 .. ..some more data... last equivlent to your problem: select count(pkdisburseid) from trndisburse_TMP m where grossamt=6000 Group by pkdisburseid; select count(pkdisburseid),grossamt from trndisburse_TMP m where grossamt=6000 Group by pkdisburseid,grossamt; Thanks , abhisehk choudhary www.tech4urhelp.blogspot.com From: Stefan Kuhn stef...@web.de To: mysql@lists.mysql.com Sent: Saturday, 14 April 2012 4:51 PM Subject: Re: Simple Query Question On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: Hi, Please help what is wrong with this simple query SELECT COUNT(key_agent) total FROM agents_consolidated WHERE total = 180 Thanks. You need to use having instead of where, see the documentation. Stefan Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Need Help Writing Simple Query
On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong cuong.m...@vienthongso.com wrote: Hi Mark, Please test this query: select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1, (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 from test1; - test1 table: col1v_idh_id America 1 2 - test2 table: id name 2 SAM 1 UNCLE - Original Message - From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Monday, July 26, 2010 8:29:00 AM Subject: Need Help Writing Simple Query I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com Thanks! That did the trick. Mark
Need Help Writing Simple Query
I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark
Re: Need Help Writing Simple Query
Hi Mark, Please test this query: select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1, (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 from test1; - test1 table: col1v_idh_id America 1 2 - test2 table: id name 2 SAM 1 UNCLE - Original Message - From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Monday, July 26, 2010 8:29:00 AM Subject: Need Help Writing Simple Query I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need Help Writing Simple Query
On 07/25/2010 09:29 PM, Mark Phillips wrote: I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C select a, b, c, hTable.name as hName, vTable.name as vName from Table1, Table2 as hTable, Table2 as vTable where hId = hTable.ID and vID = vTable.ID or select a, b, c, hTable.name as hName, vTable.name as vName from Table1 left join Table2 as hTable on hID = hTable.ID left join Table2 as vTable on vID = vTable.ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple query takes forever
Here's the table definition, in case that helps:
| qs| CREATE TABLE `qs` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`province` enum('BC','AB','SK','MB') collate latin1_general_ci NOT NULL,
`s_ts_r_m` varchar(15) collate latin1_general_ci NOT NULL,
`quartersection` varchar(3) collate latin1_general_ci NOT NULL,
`latitude` decimal(8,6) NOT NULL,
`longitude` decimal(10,6) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `s_ts_r_m` (`s_ts_r_m`),
KEY `latlng` (`latitude`,`longitude`),
SPATIAL KEY `coord` (`coordinates`)
) ENGINE=MyISAM AUTO_INCREMENT=1467939 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci |
On 2009-12-31, at 9:25 PM, René Fournier wrote:
OK, this problem (for me at least) is becoming a dead horse which I beat
daily. I was having problems, I thought, with a spatial query running
ridiculously slowly. Turns out the previous non-spatial index query I was
using is also running super slow for reasons I can't figure out. So, to recap:
I'm running a basic query (indexed, no joins) on a table with 1.5 million
rows, returning ~80 rows. Executes in 45-75 seconds. On other, different
tables with indexes I typically see queries run in 0.01 seconds. So something
wrong. Explain:
mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection, SQRT( POW( 69.1 * ( latitude - 50.444936 ) , 2 ) +
POW( 69.1 * ( -103.813919 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS
distance FROM qs WHERE latitude BETWEEN 50.41949251 AND 50.47036582 AND
longitude BETWEEN -103.85384082 AND -103.77395424\G;
*** 1. row ***
id: 1
select_type: SIMPLE
table: qs
type: range
possible_keys: latlng
key: latlng
key_len: 9
ref: NULL
rows: 10434
Extra: Using where
1 row in set (0.00 sec)
mysql DESCRIBE qs;
++---+--+-+-++
| Field | Type | Null | Key | Default | Extra
|
++---+--+-+-++
| id | mediumint(8) unsigned | NO | PRI | NULL|
auto_increment |
| province | enum('BC','AB','SK','MB') | NO | | NULL|
|
| s_ts_r_m | varchar(15) | NO | MUL | NULL|
|
| quartersection | varchar(3)| NO | | NULL|
|
| latitude | decimal(8,6) | NO | MUL | NULL|
|
| longitude | decimal(10,6) | NO | | NULL|
|
| coordinates| point | NO | MUL | NULL|
|
++---+--+-+-++
7 rows in set (0.00 sec)
What am I missing?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub...@renefournier.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple query takes forever
At 08:25 AM 12/31/2009, you wrote:
OK, this problem (for me at least) is becoming a dead horse which I beat
daily. I was having problems, I thought, with a spatial query running
ridiculously slowly. Turns out the previous non-spatial index query I was
using is also running super slow for reasons I can't figure out. So, to recap:
I'm running a basic query (indexed, no joins) on a table with 1.5 million
rows, returning ~80 rows. Executes in 45-75 seconds. On other, different
tables with indexes I typically see queries run in 0.01 seconds. So
something wrong. Explain:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection, SQRT( POW( 69.1 * (
latitude - 50.444936 ) , 2 ) + POW( 69.1 * ( -103.813919 - longitude ) *
COS( latitude / 57.3 ) , 2 ) ) AS distance FROM qs WHERE latitude BETWEEN
50.41949251 AND 50.47036582 AND longitude BETWEEN -103.85384082 AND
-103.77395424\G;
*** 1. row ***
id: 1
select_type: SIMPLE
table: qs
type: range
possible_keys: latlng
key: latlng
key_len: 9
ref: NULL
rows: 10434
Extra: Using where
1 row in set (0.00 sec)
mysql DESCRIBE qs;
++---+--+-+-++
| Field | Type | Null | Key | Default |
Extra |
++---+--+-+-++
| id | mediumint(8) unsigned | NO | PRI | NULL|
auto_increment |
| province | enum('BC','AB','SK','MB') | NO | |
NULL||
| s_ts_r_m | varchar(15) | NO | MUL |
NULL||
| quartersection | varchar(3)| NO | |
NULL||
| latitude | decimal(8,6) | NO | MUL |
NULL||
| longitude | decimal(10,6) | NO | |
NULL||
| coordinates| point | NO | MUL |
NULL||
++---+--+-+-++
7 rows in set (0.00 sec)
What am I missing?
...Rene
Rene,
Hard to say. I'd recommend creating a temporary table with the same
structure as the old table except with lattitude and longtitude as
Double(10,6). It might have something to do with Decimal() which may be
storing the number as a string.
create table qstmp like qs;
alter table qstmp change column latitude latitude double(10,6), longitude
longitude double(10,6);
insert into qstmp select * from qs;
Now do your query.
(Sorry of there are any syntax errors)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple Query Question
Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple Query Question
Hi, Thanks, I just checked and it was a memcache that was caching the output. See I knew it was a simple solution ;) Thanks for the effort everyone and sorry for wasting time. Regards Ian 2009/12/17 Aleksandar Bradaric leann...@gmail.com Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian
Re: Simple query slow on large table
Simon Kimber schrieb: Hi Everyone, I'm having a very simple query often take several seconds to run and would be hugely grateful for any advice on how i might spped this up. The table contains around 500k rows and the structure is as follows: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | ID| int(11) | | PRI | NULL | auto_increment | | siteid| int(11) | | MUL | 0 | | | sender| varchar(255) | | | | | | subject | varchar(255) | | MUL | | | | message | text | | | | | | datestamp | timestamp| YES | MUL | CURRENT_TIMESTAMP | | | msgtype | int(1) | | MUL | 0 | | | isread| int(1) | | | 0 | | +---+--+--+-+---+--- -+ I have indexes on siteid, datestamp and msgtype. Queries such as the following are constantly appearing in the slow queries log: SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY datestamp DESC LIMIT 5; An EXPLAIN on the above query returns: ++-+---+--+++--- --+---+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---+--+++--- --+---+--+-+ | 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid | 4 | const | 1940 | Using where; Using filesort | ++-+---+--+++--- --+---+--+-+ Shouldn't MySQL be using the datestamp index for sorting the records? When I remove the ORDER BY clause the query is considerably faster. Do I need to do something to make sure it using the index when sorting? Any help will be greatly appreciated! Regards hi Simon, you can try a join see http://www.artfulsoftware.com/infotree/queries.php for hints. sql is pretty bad for time series data. IMHO is the most obvious thing to reduce the number entries in your table. (do you realy need ID when you have a timestamp ?, etc) Otherwise the other stuff like: myisam instead of immodb but this depends on your requirements. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple query slow on large table
On Tue, Aug 18, 2009 at 5:08 AM, Simon Kimbersi...@internetstuff.ltd.uk wrote: I have indexes on siteid, datestamp and msgtype. Queries such as the following are constantly appearing in the slow queries log: SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY datestamp DESC LIMIT 5; Read the explanation of ORDER BY optimization: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html As it explains, you aren't providing a key it can use. If you create a multi-column index on siteid, msgtype, and datestamp, that will probably fix it. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why is simple query not using index?
On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins per...@elem.com wrote: My guess would be that your table is too small to bother using an index on. There's some information in the MySQL docs about when it chooses to use an index. For small tables, using one makes the query slower. I think this is likely the key point. When I changed the query to: explain select count(*) from member force index (expiration) where expiration date_add(CURDATE(), interval 30 day) it gave me this: idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1|SIMPLE|member|range|expiration|expiration|4|null|26|Using where; Using index This used the index, and the number of rows addressed is the correct number of rows. I found information in the MySQL docs (7.4.5. How MySQL Uses Indexes) that says it might not use an index if it determines that a table scan would be less expensive, but nothing that says specifically when this would happen (which doesn't surprise me). - Perrin On Tue, Mar 3, 2009 at 7:58 PM, David Karr davidmichaelk...@gmail.com wrote: I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect?
Why is simple query not using index?
I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect?
Re: Why is simple query not using index?
My guess would be that your table is too small to bother using an index on. There's some information in the MySQL docs about when it chooses to use an index. For small tables, using one makes the query slower. - Perrin On Tue, Mar 3, 2009 at 7:58 PM, David Karr davidmichaelk...@gmail.com wrote: I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIER INDEX_NAME ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC NON_UNIQUE TYPE CARDINALITY PAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple data, simple query giving me a brain-ache
The * was just because I am lazy, the point of my question was related to
the original post, where he was using Group by and Order by.
My question was really Why not just use order by?
So (with my original question) I should have written:
SELECT firstname, lastname from HowToExample ORDER BY Ranking;
I've been designing, writing, and maintaining software for over 20 years.
I understand the potential problem/confusion with using an * in a program,
a change to the structure of the row, adding a column, deleting a column,
and so on would break all programs using a * on that specific table.
Now I've learned that using a * in a email can also cause confusion about
what the original question.
:-)
Cheers,
Michael.
Because * could be anything, and you could change the structure of the
table
after making the program, and the order of the columns might change, and
then the program might not work.
But if you'll use
select first_name, last_name, address, etc from table_name;
then even if you will change the order of the columns or insert new
columns
between the existing columns, your program will surely work.
Of course, if you don't get the columns by their position in your program,
but by their name, then your program would work in any case.
For example, if in perl you would do:
while (my $row = $sth-fetchrow_hashref) {
print $row-{first_name};
print $row-{last_name};
}
then you could use select * from table_name without any problem.
Octavian
- Original Message -
From: mich...@j3ksolutions.com
To: MySQL General List mysql@lists.mysql.com
Sent: Saturday, February 14, 2009 3:21 AM
Subject: Re: Simple data, simple query giving me a brain-ache
I'm a SQL novice, and I'v been looking at this, and I know I shouldn't,
but I was 'Thinking';
Why wouldn't you do the following?
SELECT * from HowToExample ORDER BY Ranking;
Just curious,
Michael.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=orasn...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simple data, simple query giving me a brain-ache
Ok, I have a select statement which must return the distinct names, sorted by ranking (lowest to highest). Seems absurdly simple, right, and I'm sure it would be... look at this example CREATE TABLE IF NOT EXISTS HowToExample ( Name VARCHAR( 32 ), Ranking INTEGER ) ENGINE=MyISAM; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 1 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 4 ); In this case it works correctly, and I get First, Second, Last! YAY SELECT Name, Ranking FROM HowToExample GROUP BY Name ORDER BY Ranking; If we CHANGE the contents as follows, however, we get : TRUNCATE TABLE HowToExample; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 4 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 1 ); Then the same SELECT query fails, and gives me Second, Last, First! I can't determine an appropriate HAVING clause nor any method of getting this in a single query... Any clues? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple data, simple query giving me a brain-ache
Timothy, On Fri, Feb 13, 2009 at 4:45 PM, Little, Timothy tlit...@thomaspublishing.com wrote: Ok, I have a select statement which must return the distinct names, sorted by ranking (lowest to highest). Seems absurdly simple, right, and I'm sure it would be... look at this example CREATE TABLE IF NOT EXISTS HowToExample ( Name VARCHAR( 32 ), Ranking INTEGER ) ENGINE=MyISAM; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 1 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 4 ); In this case it works correctly, and I get First, Second, Last! YAY SELECT Name, Ranking FROM HowToExample GROUP BY Name ORDER BY Ranking; If we CHANGE the contents as follows, however, we get : TRUNCATE TABLE HowToExample; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 4 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 1 ); Then the same SELECT query fails, and gives me Second, Last, First! I can't determine an appropriate HAVING clause nor any method of getting this in a single query... I'm not 100% sure I understand you, but I notice that you're selecting non-grouped data in a grouped query: http://www.xaprb.com/blog/2006/03/11/many-to-one-problems-in-sql/ Try this: select name, max(ranking) as ranking_max order by ranking_max; -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple data, simple query giving me a brain-ache
I'm a SQL novice, and I'v been looking at this, and I know I shouldn't, but I was 'Thinking'; Why wouldn't you do the following? SELECT * from HowToExample ORDER BY Ranking; Just curious, Michael. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple Query
I tried this but it is not working. I'm not very familiar with subqueries as you can see. insert into table_2 ( id, value ) values ( (select id from table_1), '1' ); insert into table_2 (id,value) select id,1 from table_1; PB Ben Wiechman wrote: I need help writing what is probably a rather simple query. I have two tables. The first contains several columns, but most importantly an id column. The second is has two columns, an id that corresponds with the id in the first table, and a value. For every row in the first table I'd like to insert a row into the second with a set value. I tried this but it is not working. I'm not very familiar with subqueries as you can see. insert into table_2 ( id, value ) values ( (select id from table_1), '1' ); Ben Wiechman [EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.15.30/1126 - Release Date: 11/12/2007 12:56 PM
Index usage for simple query
Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) Thanks, Colin Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. Baron You're right that it is pre version 5. It's MySQL 4.1. Any particular reason this would make a difference in such a simple case? The table is about a million rows so I doubt MySQL would decide it's cheaper to scan the whole table, or at least if it is I can't figure out why! Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. Thanks very much for your help! Colin Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. Baron You're right that it is pre version 5. It's MySQL 4.1. Any particular reason this would make a difference in such a simple case? The table is about a million rows so I doubt MySQL would decide it's cheaper to scan the whole table, or at least if it is I can't figure out why! Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Optimizing or is pretty complicated, because (in general) the alternatives may access different columns etc. I know your case is a simple one, but still ... Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | [[...]] Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Have you tried BETWEEN ? As long as your source values are continuous, it would be applicable, and source BETWEEN 8 and 9 is still a single condition. I do not claim it *will* be optimized better, but it *might*. If you also need to query for source value lists with gaps, things become different. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Joerg Bruehe wrote: Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Optimizing or is pretty complicated, because (in general) the alternatives may access different columns etc. I know your case is a simple one, but still ... Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | [[...]] Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Have you tried BETWEEN ? As long as your source values are continuous, it would be applicable, and source BETWEEN 8 and 9 is still a single condition. I do not claim it *will* be optimized better, but it *might*. If you also need to query for source value lists with gaps, things become different. Whoops, I overlooked the obvious ;-) Thanks for catching this. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What should be a simple query...
Try this: SELECT RMAs.rma_id FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id = rma_line_items.rma_id GROUP BY RMAs.rma_id HAVING COUNT(*) 1 On Sep 10, 2007, at 11:36 PM, Mike Mannakee wrote: I have two tables, one called RMAs and the other called rma_line_items. The first one has the general details of the RMA (Return Merchandise Authorization) , the second holds the details of each item being returned. What I want is a listing of the RMA ids (which are unique in the RMAs table) which have more than one line item in the corresponding table. So I'm using: SELECT * FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id IN (SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1) and it's netting me nothing, which I know is not true. So to investigate I just ran the subselect: SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1 and I find it's not giving me but one row, the first one to match having more than one item. But there are plenty more RMAs that have more than one entry in the rma_line_items table and I need to get at them. What am I doing wrong? Any ideas? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What should be a simple query...
I have two tables, one called RMAs and the other called rma_line_items. The first one has the general details of the RMA (Return Merchandise Authorization) , the second holds the details of each item being returned. What I want is a listing of the RMA ids (which are unique in the RMAs table) which have more than one line item in the corresponding table. So I'm using: SELECT * FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id IN (SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1) and it's netting me nothing, which I know is not true. So to investigate I just ran the subselect: SELECT rma_id FROM rma_line_items HAVING COUNT(*) 1 and I find it's not giving me but one row, the first one to match having more than one item. But there are plenty more RMAs that have more than one entry in the rma_line_items table and I need to get at them. What am I doing wrong? Any ideas? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to optimize this simple query with join?
I have to tables (on mysql 5.0.22): Table: shelf CREATE TABLE `shelf` ( `isbn` varchar(10) NOT NULL default '', `product_type` char(1) default NULL, `title` varchar(150) NOT NULL default '', (...) PRIMARY KEY (`isbn`), KEY `publ_date` (`publ_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `annotations` ( `isbn` varchar(10) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`isbn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 of course the 'isbn' is a 'foreing key' for the two tables; I'd like to have a list of all my items with the description (if it's present); then something like SELECT * FROM shelf LEFT JOIN annotations ON (shelf.isbn=annotation.isbn) ; it works but it's SLOW! ++-+-+--+---+- +-+--+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+-+--+---+- +-+--+-+---+ | 1 | SIMPLE | shelf | ALL | NULL | NULL| NULL| NULL | 1343001 | | | 1 | SIMPLE | annotations | ref | PRIMARY | PRIMARY | 12 | zcommerce.shelf.isbn | 1 | | ++-+-+--+---+- +-+--+-+---+ why the primary key is not used as index to 'speedup' the join? what's wrong? may be is because using that I'm making a 'dump' of the entire table? thank you in advance for all! bye bye marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Having problems with what appears to be a simple query.
I have a table with numerous columns but needing to perform a query based on three columns: Lab_number, result, release_time. What I want to do is search for lab_number where there is a result but not released. The problem that is making this difficult for me, is that there are multiple entries for the same lab_number, due to a lab_number having 1 or more tests being performed on it. The search I really want to do is find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. *---* |lab_number | result |release_time | | 1 | 10 |-00-00 00:00:00| | 1 | 20 |-00-00 00:00:00| | 2 | 5 |-00-00 00:00:00| | 2 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| *---* So the query I want will only return 1, as 2 is not yet complete. The attempts I have made so far will return 2 as well. The thing that is really annoying me is that I know I will kick myself when I see a solution! Thanks Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL There's a bit of discussion of this query pattern at http://www.artfulsoftware.com/queries.php nder 'Joins', 'The [Not] Exists Query Pattern'. PB - Daniel Smith wrote: I have a table with numerous columns but needing to perform a query based on three columns: Lab_number, result, release_time. What I want to do is search for lab_number where there is a result but not released. The problem that is making this difficult for me, is that there are multiple entries for the same lab_number, due to a lab_number having 1 or more tests being performed on it. The search I really want to do is find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. *---* |lab_number | result |release_time | | 1 | 10 |-00-00 00:00:00| | 1 | 20 |-00-00 00:00:00| | 2 | 5 |-00-00 00:00:00| | 2 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| *---* So the query I want will only return 1, as 2 is not yet complete. The attempts I have made so far will return 2 as well. The thing that is really annoying me is that I know I will kick myself when I see a solution! Thanks Danny -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote: Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL Playing around with this examples, I realise I forgot to mention something I didn't make clear in my original post. The reason for doing the search is to find completed lab_numbers which have not yet been released, hence the -00-00 00:00:00 timestamp. Using the examples you have given me don't seem to be working in the way I'd hoped. I'm just changing the tbl to the correct table name and seem to be getting results that are released, rows with no results and the completed results awaiting release. Using this query, based on yours: SELECT t1.lab_number,t1.result,t1.release_time FROM requesting_test_results t1 LEFT JOIN requesting_test_results t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL AND t1.result !='' AND t1.release_time =' 000-00-00 00:00:00' GROUP BY t1.lab_number I get 2 records, one is a lab_number which is completed but not released (lab_number 999), the other is a lab_number that has is only partially completed (3265). Removing the GROUP statement returns 6 records, four entries for 999 and 2 for 3265, though there are still 8 rows for 3265 with no result. I will try and get my head around joining to the same table in meantime. Thanks for prompt answer. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB Daniel Smith wrote: On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote: Daniel, find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. SELECT t1.lab,t1.res,t1.dt FROM tbl t1 WHERE NOT EXISTS( SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL ); The decorrelated version uses an exclusion join, runs faster but looks a bit strange: SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL Playing around with this examples, I realise I forgot to mention something I didn't make clear in my original post. The reason for doing the search is to find completed lab_numbers which have not yet been released, hence the -00-00 00:00:00 timestamp. Using the examples you have given me don't seem to be working in the way I'd hoped. I'm just changing the tbl to the correct table name and seem to be getting results that are released, rows with no results and the completed results awaiting release. Using this query, based on yours: SELECT t1.lab_number,t1.result,t1.release_time FROM requesting_test_results t1 LEFT JOIN requesting_test_results t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL AND t1.result !='' AND t1.release_time =' 000-00-00 00:00:00' GROUP BY t1.lab_number I get 2 records, one is a lab_number which is completed but not released (lab_number 999), the other is a lab_number that has is only partially completed (3265). Removing the GROUP statement returns 6 records, four entries for 999 and 2 for 3265, though there are still 8 rows for 3265 with no result. I will try and get my head around joining to the same table in meantime. Thanks for prompt answer. Danny No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
On Wed, 2007-01-17 at 11:47 -0600, Peter Brawley wrote: Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ SELECT t1.lab_number,t1.result,t1.release_time FROM tbl t1 LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB This works!! It seems it was my defaults for my original table that was causing me my problems. Sorry for troubling you, thanks once again for the prompt solution. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query
Hope this helps: select min(ctime), max(ctime) from tbl_a where ctime != -00-00 00:00:00 Best regards, Diego - Original Message - From: Xiaobo Chen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 13, 2006 11:17 AM Subject: simple query Hi, all I have a table which has a column with time. It's format is like -00-00 00:00:00 (default value). I want to get the minimum and maximum values for this cloumn. Obviously, -00-00 00:00:00 isn't the minimum value I want. What I did for the minimum time, suppose the column name is ctime, the table name is tbl_a: select ctime form tbl_a where ctime-00-00 00:00:00 order by ctime limit 1; Using this query, I am able to get the minimum time value. But what is the query for max time value? I guess it's something about reverseing the order by, but I couldn't find it. Could anyone please give me a hand? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Información de NOD32 1.1440 (20060312) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help regarding a simple query
Hi, I am searching for a query where i can have pattern matching without considering the cases.You can consider the following example for detailed description of what i want exactly. Let my table X consists of following data Name --- venu venup venugopla VenugOpal VENU papasani papasni pvenu Now i need to get all the records which consists of the string venu(case should not be considered either case should be).i.e i should get 1,2,3,4,5,8 records I will be very much thankful if any of you give me the query for this. Thanks in Advance, Regards, venu. (sorry for my poor English)
RE: Help regarding a simple query
-Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 10:33 To: mysql@lists.mysql.com Subject: Help regarding a simple query Hi, I am searching for a query where i can have pattern matching without considering the cases.You can consider the following example for detailed description of what i want exactly. Let my table X consists of following data Name --- venu venup venugopla VenugOpal VENU papasani papasni pvenu Now i need to get all the records which consists of the string venu(case should not be considered either case should be).i.e i should get 1,2,3,4,5,8 records I will be very much thankful if any of you give me the query for this. Thanks in Advance, Regards, venu. (sorry for my poor English) Won't this work? Select * from X where name like '%venu%' jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help regarding a simple query
Now i need to get all the records which consists of the string
venu(case should not be considered either case should be).i.e i should get
1,2,3,4,5,8 records
A simple way is ...
...
WHERE LOCATE('venu', col_name ) 0
...
or if the column is [VAR]BINARY, LOCATE('venu',CAST(col_name AS CHAR))
for case insensitivity.
PB
-
VenuGopal Papasani wrote:
Hi,
I am searching for a query where i can have pattern matching without
considering the cases.You can consider the following example for detailed
description of what i want exactly.
Let my table X consists of following data
Name
---
venu
venup
venugopla
VenugOpal
VENU
papasani
papasni
pvenu
Now i need to get all the records which consists of the string
venu(case should not be considered either case should be).i.e i should get
1,2,3,4,5,8 records
I will be very much thankful if any of you give me the query for this.
Thanks in Advance,
Regards,
venu.
(sorry for my poor English)
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help regarding a simple query
-Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 11:48 To: Jeff Subject: Re: Help regarding a simple query Hi Jeff, This is venu again.Last mail i did not include a constraint that is what irritating me most.Actually if i got venu-kkk I should not get that venu-kkk. This was the query actually i want. Can you please give me teh query for that Regards, venu. Please post all responses to the mailing list, not directly to another person. the % is a wild card character Name -- Venu VENU XVENU yVeNu Venuzztest select * from X where Name like '%venu%' returns: Venu VENU XVENU yVeNu Venuzztest select * from X where Name like 'venu%' returns: Venu VENU Venuzztest select * from X where Name like '%venu' returns: Venu VENU XVENU yVeNu Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unclear on UPDATE versus INSERT, and a simple query that is not working
MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have two tables, one old, and one new. In both tables I have a column called active, which is either 0 or 1, to denote whether a user is to receive email or not. I am migrating all the data from the old table to the new. I want to make sure that the active column is identical, so I simply want to copy the fields from the old table to the new, overwriting what may exist in the new. I want to match the fields by using the member ID, called id in the old table and MEMBER_ID in the new. This is the statement I thought would work. UPDATE forum_members( active ) SELECT active FROM members WHERE forum_members.MEMBER_ID = members.id But MySQL tells me: |You have an error in your SQL syntax| The Questions: What do I need to do to correct the syntax of the statement? What I've done so far: I have looked at the online MySQL manual for the correct syntax for INSERT and UPDATE. But despite reading them, I'm still not sure which I want to be using, and how. Any assistance is much appreciated. Thank you. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear on UPDATE versus INSERT, and a simple query that is not working
2005/9/15, Dave [EMAIL PROTECTED]: MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have two tables, one old, and one new. In both tables I have a column called active, which is either 0 or 1, to denote whether a user is to receive email or not. I am migrating all the data from the old table to the new. I want to make sure that the active column is identical, so I simply want to copy the fields from the old table to the new, overwriting what may exist in the new. I want to match the fields by using the member ID, called id in the old table and MEMBER_ID in the new. This is the statement I thought would work. UPDATE forum_members( active ) SELECT active FROM members WHERE forum_members.MEMBER_ID = members.id I'm sure other on the list will explain in details, but I'll do short. I understood that forum_members is the new table, members the old one. So : UPDATE forums_members,members SET forums_members.active=members.active WHERE forums_members.member_id = members.id -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear on UPDATE versus INSERT, and a simple query that is not working
UPDATE forums_members,members SET forums_members.active=members.active WHERE forums_members.member_id = members.id Oh, I see. I now feel a little foolish as I should have grasped that. For some reason I assumed that by specifying where to get the data from, it would be assumed that's what the data was set to. But of course that's not the case. One specifies what to get, and then separately specifies what to set. Thank you for clearing that up. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query on an indexed col in big table is extremely slow
Zhe Wang [EMAIL PROTECTED] wrote on 07/28/2005 10:40:08 AM: Hi, there, I am have a hard time figuring out why a simple query is extremely slow. I would greatly appreciate if you can shed some light! The table is in InnoDB: CREATE TABLE `rps_hits` ( `gi` int(10) unsigned NOT NULL default '0', `cddid` int(10) unsigned NOT NULL default '0', `bit_score` float NOT NULL default '0', `evalue` double NOT NULL default '0', `identity` smallint(5) unsigned NOT NULL default '0', `query_from` smallint(5) unsigned NOT NULL default '0', `query_to` smallint(5) unsigned NOT NULL default '0', `hit_from` smallint(5) unsigned NOT NULL default '0', `hit_to` smallint(5) unsigned NOT NULL default '0', `hit_len` smallint(5) unsigned NOT NULL default '0', `align_len` smallint(5) unsigned NOT NULL default '0', `bz_alignment` blob NOT NULL, KEY `gi` (`gi`), KEY `cddid` (`cddid`), KEY `evalue` (`evalue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 It is a big table with more than 60 million rows, the rps_hits.ibd file is 22 G. All the queries I mention below were run when no other job were running against the database. I did a very simple query against the table: select gi, cddid, evalue from rps_hits where cddid=3161. It took 4 minutes and 29.90 seconds to pull out only 1952 rows. Whereas another simply query on gi select gi, cddid, evalue from rps_hits where gi=393396 pulled out 1532 rows in just 0.09 second. Expalin the above query gave: mysql explain select gi, cddid, evalue from rps_hits where cddid=3161\G *** 1. row *** id: 1 select_type: SIMPLE table: rps_hits type: ref possible_keys: cddid key: cddid key_len: 4 ref: const rows: 1376 Extra: Using where 1 row in set (0.06 sec) It looks OK. Then I did show index from rps_hits, all the index properties for each of the 3 indexes are the same except cardinality. Column gi has a cardinality of 3084286 and cddid has 14. Though a specific select count(distinct cddid) from rps_hits returned 11156. Since 11156 unique cddid is less than 0.01% of the total number of rows in the table, I believed the server decided to do a full table scan (does anyone know the exact percentage number of the total counts that MySQL uses as a criteria when deciding to do a FTS?) I then use use index in the query after I made sure the query and index were no longer in the cache : select gi, cddid, evalue from rps_hits use index (cddid) where cddid=3161. It still took a long time (2 min 59.79 sec) to return the 1952 rows. I also noticed that a simple query on evalue like select gi, cddid, evalue from rps_hits where evalue=1.97906; is also extremely slow even force index was used (5.78 sec for 56 rows). I have not been able to figure out what went wrong. Since the index on gi worked fine, I am just wondering if the slowness is caused by the large size of the table and that the indexes on cddid and evalue were created as second index and third index respectively in create table. However I have another huge table with blob column and with comparable size and number of rows to this rps_hits table, if I searched on the third index, it was very fast. This problem really troubled me and I would greatly appreciate if anyone could give me a hint. Thank you in advance! Regards, Zhe My first idea is to have you run ANALYZE TABLE against your table. Analyze table will update your index statistics (like cardinality). Your index cache may be too small or your the actual index may be too large to accomodate it in memory all at the same time. That means that you are using swap space to store part of your indexes on disk and it may be takeing just that much more time to crawl a paged index compared to one that can reside completely (or mostly) in memory. How many records are returned is only important if you and your server are connected by a slow network or if you are receiving HUGE quantities of data (gigabytes) in your results. The slower the network, the longer it will take to transfer the data from the server to you. However, most modern networks take that factor out of consideration for issues that you are describing. My suspicion is that you memory paging and cache sizes and disk performance are your most likey bottlenecks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: simple query on an indexed col in big table is extremely slow
Hi, Shawn, Thanks a lot for your reply. Running analyze table didn't help much since this table has not been updated after being built. The table has 3 single indexes. What puzzled me was that the queries against the first index were fast, only slow if against the second or third index. The column types are the same for the first and second index (int(10) unsigned). This is my first time to encounter a slow query on an indexed column. I have another table which has about 750 millions rows, a search against the index column (int(9) unsigned) has lightening speed. Does it mean the key cache on my machine is big enough? Below are the values of the cache variables. Which variable do you think need to be boosted up? Again, thank you very much or your help! +--+--+ | Variable_name| Value| +--+--+ | bdb_cache_size | 8388600 | | binlog_cache_size| 32768| | have_query_cache | YES | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | max_binlog_cache_size| 18446744073709551615 | | query_cache_limit| 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | table_cache | 524288 | | thread_cache_size| 512 | +--+--+ Regards, Zhe [EMAIL PROTECTED] wrote: Zhe Wang [EMAIL PROTECTED] wrote on 07/28/2005 10:40:08 AM: Hi, there, I am have a hard time figuring out why a simple query is extremely slow. I would greatly appreciate if you can shed some light! The table is in InnoDB: CREATE TABLE `rps_hits` ( `gi` int(10) unsigned NOT NULL default '0', `cddid` int(10) unsigned NOT NULL default '0', `bit_score` float NOT NULL default '0', `evalue` double NOT NULL default '0', `identity` smallint(5) unsigned NOT NULL default '0', `query_from` smallint(5) unsigned NOT NULL default '0', `query_to` smallint(5) unsigned NOT NULL default '0', `hit_from` smallint(5) unsigned NOT NULL default '0', `hit_to` smallint(5) unsigned NOT NULL default '0', `hit_len` smallint(5) unsigned NOT NULL default '0', `align_len` smallint(5) unsigned NOT NULL default '0', `bz_alignment` blob NOT NULL, KEY `gi` (`gi`), KEY `cddid` (`cddid`), KEY `evalue` (`evalue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 It is a big table with more than 60 million rows, the rps_hits.ibd file is 22 G. All the queries I mention below were run when no other job were running against the database. I did a very simple query against the table: select gi, cddid, evalue from rps_hits where cddid=3161. It took 4 minutes and 29.90 seconds to pull out only 1952 rows. Whereas another simply query on gi select gi, cddid, evalue from rps_hits where gi=393396 pulled out 1532 rows in just 0.09 second. Expalin the above query gave: mysql explain select gi, cddid, evalue from rps_hits where cddid=3161\G *** 1. row *** id: 1 select_type: SIMPLE table: rps_hits type: ref possible_keys: cddid key: cddid key_len: 4 ref: const rows: 1376 Extra: Using where 1 row in set (0.06 sec) It looks OK. Then I did show index from rps_hits, all the index properties for each of the 3 indexes are the same except cardinality. Column gi has a cardinality of 3084286 and cddid has 14. Though a specific select count(distinct cddid) from rps_hits returned 11156. Since 11156 unique cddid is less than 0.01% of the total number of rows in the table, I believed the server decided to do a full table scan (does anyone know the exact percentage number of the total counts that MySQL uses as a criteria when deciding to do a FTS?) I then use use index in the query after I made sure the query and index were no longer in the cache : select gi, cddid, evalue from rps_hits use index (cddid) where cddid=3161. It still took a long time (2 min 59.79 sec) to return the 1952 rows. I also noticed that a simple query on evalue like select gi, cddid, evalue from rps_hits where evalue=1.97906; is also extremely slow even force index was used (5.78 sec for 56 rows). I have not been able to figure out what went wrong. Since the index on gi worked fine, I am just wondering if the slowness is caused by the large size
Re: Simple query? Is it possible?
Vaidas Zilionis wrote: [...] Example items are displayed 100 in page, and i display 20 pages numbers 1 ... 4[5] 6 x doomain.con/items.php?page=5 and i get all result here with limit 400,100 Yes, with PHP it would be something like this: $items_per_page = 100; $limit_clause = ((int)$page-1)*$items_per_page.','.$items_per_page; If i use it doomain.con/items.php?showid=45 (example he is in 7 page) i want to see same table with page numbers and ect. And record must be in 7 page. 1 ... 6 [7] 8 x Why not just create your links like this: doomain.con/items.php?showid=45page=7 At this stage, when you create the link, you allready know what page you are on. Why waste time and try to calculate it again? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Simple query? Is it possible?
Hello Roger, Monday, May 9, 2005, 2:56:18 PM, you wrote: Heh if i know page i would haven't problem. but i need to get also correct page with link doomain.con/items.php?showid=45 it can be anywhere! :) I making web application with data binding (IE stuff) data is loading very fast, can be 1000 and 100 of rows, it's not problem, and user interface with data loads very fast. if i use scroll, JS count's what possition is on row, and if needed loading data from server. But now i need to to simple trick, SelectRowWhereId(1525) script must now position of that row in database with same WHERE and ORDER. so i never know page number. :) RB Vaidas Zilionis wrote: RB [...] Example items are displayed 100 in page, and i display 20 pages numbers 1 ... 4[5] 6 x doomain.con/items.php?page=5 and i get all result here with limit 400,100 RB Yes, with PHP it would be something like this: RB $items_per_page = 100; RB $limit_clause = RB ((int)$page-1)*$items_per_page.','.$items_per_page; If i use it doomain.con/items.php?showid=45 (example he is in 7 page) i want to see same table with page numbers and ect. And record must be in 7 page. 1 ... 6 [7] 8 x RB Why not just create your links like this: RB doomain.con/items.php?showid=45page=7 RB At this stage, when you create the link, you allready know what page you RB are on. Why waste time and try to calculate it again? -- -- Vaidas ilionis Mobile: +370 616 91393 ICQ: 35174940 Website: www.zilionis.net Project: www.sitesupra-tools.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[3]: Simple query? Is it possible? (hm.. solution?, maybe can be better?)
Hello Vaidas, Monday, May 9, 2005, 3:08:26 PM, you wrote: hm founded something intresting :) set @mynr:=0; Select @mynr as nr,table.id from table where @a:=IF((table.id=0),0,(@a+1)) having table.id=518 order by id strange IF hack :) main problem what this metod not fast. tested in table with 35024 rows, result i got only after 0.7s! what i can improve? VZ Hello Roger, VZ Monday, May 9, 2005, 2:56:18 PM, you wrote: VZ Heh if i know page i would haven't problem. VZ but i need to get also correct page with link VZ doomain.con/items.php?showid=45 VZ it can be anywhere! :) VZ I making web application with data binding (IE stuff) data is loading VZ very fast, can be 1000 and 100 of rows, it's not problem, and user VZ interface with data loads very fast. VZ if i use scroll, JS count's what possition is on row, and if needed VZ loading data from server. VZ But now i need to to simple trick, SelectRowWhereId(1525) VZ script must now position of that row in database with same WHERE and VZ ORDER. VZ so i never know page number. VZ :) RB Vaidas Zilionis wrote: RB [...] Example items are displayed 100 in page, and i display 20 pages numbers 1 ... 4[5] 6 x doomain.con/items.php?page=5 and i get all result here with limit 400,100 RB Yes, with PHP it would be something like this: RB $items_per_page = 100; RB $limit_clause = RB ((int)$page-1)*$items_per_page.','.$items_per_page; If i use it doomain.con/items.php?showid=45 (example he is in 7 page) i want to see same table with page numbers and ect. And record must be in 7 page. 1 ... 6 [7] 8 x RB Why not just create your links like this: RB doomain.con/items.php?showid=45page=7 RB At this stage, when you create the link, you allready know what page you RB are on. Why waste time and try to calculate it again? VZ -- VZ -- VZ Vaidas ilionis VZ Mobile: +370 616 91393 VZ ICQ: 35174940 VZ Website: www.zilionis.net VZ Project: www.sitesupra-tools.net -- -- Vaidas ilionis Mobile: +370 616 91393 ICQ: 35174940 Website: www.zilionis.net Project: www.sitesupra-tools.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is this simple query so slow?
Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec)
Re: Why is this simple query so slow?
Have you tried optimizing or run an analyze table command on this table? Aaron wrote: Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this simple query so slow?
In the last episode (Dec 10), Aaron said: The query below takes around 8 seconds, and returns 3253 rows. Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72; 3253 rows in set (8.00 sec) Explain says: Bad word-wrapping fixed: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+-+---+-+--++--++-+ | Offers_To_Buy | 1 | subcategory |1 |subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 |subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | +---++-+--+-+---+-+--++--++-+ 11 rows in set (0.00 sec) As Donny said, your FORCE INDEX(scdd) is pessimizing you. But even moving to the subcategory key won't help you much, since you're still going to be doing 3253 random reads into your table to fetch 'id'. Try creating an index on (subcatID,ID). This will let mysql retrieve all the data it needs from an index range scan. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is this simple query so slow?
Aaron, Three things. 1. Do a show create table Offers_To_Buy 2. And why in the world would you have force index(scdd) when your where clause is on subcatID? If you can explain what you are trying to do, I'm sure many people can help you get exactly what you are looking for. 3. Why so many indexes? Do you search on every one of those fields? If not, then you are probably wasting diskspace and speed. Donny -Original Message- From: Aaron [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 8:12 PM To: [EMAIL PROTECTED] Subject: Why is this simple query so slow? Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query question
* John Mistler I have a table in which the first column is either 1 or 0. The second column is a number between 0 and 59. I need to perform a query that returns entries where: 1. IF the first column is 1, the second column is NOT 0 2. IF the first column is 0, the second column is anything. It seems simple, but I'm not getting it right. Any ideas? Try this: SELECT * FROM tab1 WHERE (col1 = 1 AND col2 0) OR (col1 = 0) When combining AND and OR, proper use of parantheses is important. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.20: index being ignored in simple query
Afternoon all
The problem: I am doing a simple query on a table, comparing 2 columns to
constants. The table is indexed with a compound index on these 2 columns.
The join optimizer only seems to notice that the first column is indexed,
and ignored the second column. The table is large (16M rows), innodb, all
details to follow.
I really need to get this query indexed. I have tried everything I can think
of, aside from upgrading to 4.1, though I will do that if there is a
realistic chance it will solve the problem, but this is a production server
and the upgrade is not straightforward to roll back from. I have read all
the pages related to query optimization and indexes in the manual, though it
could of course be something stupid I have done - in fact I hope it is. I
have tried optimize, analyze. I have tried ordering as index(Hash,Date)
instead of index(Date,Hash). Nothing will convice mysql to use the index on
the Hash column - explain stubbornly outputs 4 as the key_len instead of 25.
I have tried use index, force index, ignore index.
The server in question is a Dell 1750 with 3GB RAM dual 2.8GB h/t xeon,
mysql version 4.0.20 (mysql release, intel compiler build), slackware 9.0,
kernel 2.6.6.
Could the problem be related to memory size, ie is the index too big? If so,
how come it uses part of the index rather than none at all?
Any help very much appreciated and guruhood grovellingly acknowleged.
Thanks
Jim Page
Relevant data follows:
mysql explain SELECT * FROM r2 WHERE Date 2004-09-14 15:35:41 AND Hash
= 'xj0001J01E4k0001K0001' limit 0,10;
+---+---+---+--+-+--+-+-
+
| table | type | possible_keys | key | key_len | ref | rows| Extra
|
+---+---+---+--+-+--+-+-
+
| r2| range | hash | hash | 4 | NULL | 8354129 | Using
where |
+---+---+---+--+-+--+-+-
+
CREATE TABLE `r2` (
`RecipID` bigint(20) NOT NULL auto_increment,
`Date` timestamp(14) NOT NULL,
`AccountID` int(11) NOT NULL default '0',
`DomainID` int(11) NOT NULL default '0',
`EndUserID` int(11) NOT NULL default '-1',
`Recipient` text NOT NULL,
`MailInID` bigint(20) NOT NULL default '0',
`Status` enum('pending','virus','quarantine','spam','removed by
rule','fail','deleted','ok','rejected','sending','no
data','undefined','received','filtering','filtered','stalled') NOT NULL
default 'received',
`OtherID` bigint(20) default NULL,
`Attempts` int(11) NOT NULL default '1',
`Reported` enum('n','y') NOT NULL default 'n',
`Hash` varchar(21) NOT NULL default 'xjJkK',
`UserHash` varchar(18) default NULL,
PRIMARY KEY (`RecipID`),
KEY `MailInID` (`MailInID`),
KEY `Status` (`Status`),
KEY `hash` (`Date`,`Hash`)
) TYPE=InnoDB;
from show table status...
+-+++--++---
--+-+--+---++---
--+-++--
--+-+
| Name| Type | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+-+++--++---
--+-+--+---++---
--+-++--
--+-+
| r2 | InnoDB | Dynamic| 16609743 |155 |
2591031296 |NULL | 1784692736 | 0 |33433662093 |
NULL| NULL| NULL |
| InnoDB free: 9775104 kB |
mysql show index from r2;
+---++--+--+-+---+--
---+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---++--+--+-+---+--
---+--++--++-+
| r2| 0 | PRIMARY |1 | RecipID | A |
16688555 | NULL | NULL | | BTREE | |
| r2| 1 | MailInID |1 | MailInID| A |
16688555 | NULL | NULL | | BTREE | |
| r2| 1 | Status |1 | Status | A |
18 | NULL | NULL | | BTREE | |
| r2| 1 | hash |1 | Date| A |
18 | NULL | NULL | | BTREE | |
| r2| 1 | hash |2 | Hash| A |
11081 | NULL | NULL | | BTREE
Re: very simple query but strange results
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50% or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
I skimmed the whole article twice, including the user comments, and still
missed that :-(
Thanks for catching that Michael! That explanation makes a lot of sense, a
lot more sense than forcing there to be at least two columns in the
fulltext() column.
Rhino
- Original Message -
From: Michael Stassen [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: Kapoor, Nishikant [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Monday, August 30, 2004 7:12 PM
Subject: Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
--
--
I'm not sure.
I haven't worked with fulltext searches myself but your question
intrigued
me. I tried creating the same table you did and then ran the same query:
I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see
any
obvious error in your query based on the rules I saw there. I tried
setting
up the table that they used in their examples and it gave the same
result
that they predicted.
The big difference between your example and theirs was that their
example
had the fulltext() function applied to TWO columns, title and body,
while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must
always
be used against at least two columns? That strikes me as a very odd
design
for a function so it doesn't seem likely. However, I am at a loss to
suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50%
or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
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: how to speed up a simple query? can anyone help with an ideea?
Creating a combined index can help MySQL in using this index for both the where condition and the order by clause. Try the query with an index on cat,date and with date,cat; maybe one will be faster than the other. This partially solved my problem. Thanks a lot. However I am facing a new problem here. The query where I am ordering by a column is much more slowly than the same query NOT using SQL_CALC_FOUND_ROWS select SQL_CALC_FOUND_ROWS * from table by date desc - this query takes about 1.2 s select * from table by date desc - this query takes about 0.1 s Using explain I have noticed that the first query is using filesort and I cannot figure out why exactly... I could use count() to figure out the total number of results for the navigation and this would be defintelly much faster since now as I have tested but I was thinking if anyone else had this problem before and if you have found any work around... After some checkings In have discovered that this is more or less a bug http://bugs.mysql.com/bug.php?id=1274 But after checking very ddpe here and trying all possibilities I think this is a little different... Let me know if anyone wants to see the table structure... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to speed up a simple query? can anyone help with an ideea?
Hello! I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query?
Arthur Radulescu [EMAIL PROTECTED] wrote: I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? can anyone help with an ideea?
select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds 1. MySQL only uses one index for each table in a JOIN; this query only uses one table, so only one index is used. 2. DESC is slower than ASC 3. Try creating an index on two columns; try cat and date, and try date and cat. 4. Check EXPLAIN SELECT id,name.. to see whether the right index is used. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? can anyone help with an ideea?
1. MySQL only uses one index for each table in a JOIN; this query only uses one table, so only one index is used. 2. DESC is slower than ASC 3. Try creating an index on two columns; try cat and date, and try date and cat. 4. Check EXPLAIN SELECT id,name.. to see whether the right index is used. Thanks for yoiur answers. I only need to order by date to return the latest results and this is why I am using an index for date and one for cat since I am using it in the where condition... Do you think that combining those indexes would make it faster because I am not so sure about that... I know that desc is slower than asc but I also know that SQL server has a specific index for this so I was wondering if MySQL has something similar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to speed up a simple query? need some help here...
Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html This partially solved my problem. Thanks a lot. However I am facing a new problem here. The query where I am ordering by a column is much more slowly than the same query NOT using SQL_CALC_FOUND_ROWS select SQL_CALC_FOUND_ROWS * from table by date desc - this query takes about 1.2 s select * from table by date desc - this query takes about 0.1 s Using explain I have noticed that the first query is using filesort and I cannot figure out why exactly... I could use count() to figure out the total number of results for the navigation and this would be defintelly much faster since now as I have tested but I was thinking if anyone else had this problem before and if you have found any work around... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to speed up a simple query?
Hello! I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very simple query question
Hello i have a simple query select u.*,p.* from users u, profiles p where u.uname = p.uname and u.level != 0 Is there any tricks to make this use an index. If i do level=0 is uses an index , but != does not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query question
i have a simple query select u.*,p.* from users u, profiles p where u.uname = p.uname and u.level != 0 Is there any tricks to make this use an index. If i do level=0 is uses an index , but != does not. MySQL only uses an index if it will return less than approx. 30% of the records. It tries to guess this by looking at the cardinality of the index (the estimated number of different entries). In your case level=0 returned less than 30% of the records, so obviously level!=0 will return more than 70% of the records and MySQL desides to do a table scan (which is probably more efficient than first looking in the index and then searching for almost every single record). You can do a SELECT u.*,p.* FROM users AS u FORCE INDEX (u_level_index) JOIN profiles AS p USING (uname) WHERE u.level != 0 but this will only slow the query down. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stuck with simple query..... Plz have a look
We have two tables Table1: - ID |Name 1 |name1 2 |name2 3 |name3 4 |name4 Table2: --- PL | PC |PA| Description --- 1| 2 | 4| Some description for Project 1 2| 3 | 1| Some description for Project 2 1| 2 | 4| Some description for Project 3 4| 1 | 3| Some description for Project 4 3| 1 | 4| Some description for Project 5 2| 3 | 1| Some description for Project 6 I need the output like.. - PL-Name| PC-Name| PA-Name - name1 | name2 |name4 name2 | name3 |name1 name1 | name2 |name4 name4 | name1 |name3 name3 | name1 |name4 name2 | name3 |name1 Please guide me how can i achieve this kind of result set. Regards, Tariq
stuck with simple query..... Plz have a look
SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stuck with simple query..... Plz have a look
Looks like I trod on original post - forgot to add RE: to subject. Sorry about that! Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Should work Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stuck with simple query..... Plz have a look
Jim Page - EMF Systems Ltd wrote: SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Should work Jim (This email has been scanned for viruses by www.emf-systems.com) I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stuck with simple query..... Plz have a look
Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stuck with simple query..... Plz have a look
You are correct Jim.. This is certainly not Cartesian. Jim Page - EMF Systems Ltd [EMAIL PROTECTED] 04/07/2004 10:09 AM Please respond to Jim Page - EMF Systems Ltd To: gerald_clark [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. (This email has been scanned for viruses by www.emf-systems.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]
RE: stuck with simple query..... Plz have a look
I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
Wait, I see it now :) -Original Message- From: Luc Foisy Sent: Wednesday, April 07, 2004 10:45 AM To: [EMAIL PROTECTED] Subject: RE: stuck with simple query. Plz have a look I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Similar simple query slow down dramatically, by just select one more field, why?
N,jjkj{ zwkozz
xjDear all,
I have asked the question days before, but no one seems interested in it
Considering table imgstore, defined as
create table a (
imgid int not null,
parent int,
imgtype char(3),
img longtext,
primary key (imgid),
key searchkey (parent, imgid)
) type = innodb;
contains about 1+ rows, about 800M in size;
SQL tested:
T1) select imgid, parent from a where parent = 10;
returns 3357 rows in 0.08 sec.
T2) select imgid, parent, imgtype from a where parent = 10;
return 3357 rows in 8.32 sec.!!!
T3) select imgid, parent from a where parent = 10 order by imgid;
returns 3357 rows in 0.1 sec
T4) select imgid, parent, imgtype from a where parent = 10 order by imgid;
returns 3357 rows in 25.88 sec!!!
T5) create table za select imgid, parent, imgtype from a;
10102 rows in 1.71 sec.
T6) select imgid, parent, imgtype from za where parent = 10 order by imgid;
3357 rows in 0.02 sec.
T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in
almost same speed.
Anybody can explain my questions:
Q1- What cause the slow down, T2 vs T1 and T4 vs T3?
Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance of
T6 and T4?
Best Reguards.
Re: Similar simple query slow down dramatically, by just select one more field, why?
Oscar Yen wrote: create table a ( imgid int not null, parent int, imgtype char(3), img longtext, primary key (imgid), key searchkey (parent, imgid) ) type = innodb; T1) select imgid, parent from a where parent = 10; returns 3357 rows in 0.08 sec. T2) select imgid, parent, imgtype from a where parent = 10; return 3357 rows in 8.32 sec.!!! T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in almost same speed. Anybody can explain my questions: Q1- What cause the slow down, T2 vs T1 and T4 vs T3? Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance of T6 and T4? Best Reguards. Q1: It's explained in the MySQL Documentation under Optimization - How MySQL Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay close attention to the 5th bullet. This explains the behavior you see from T7. Q2: I have no answer for you there, Sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Similar simple query slow down dramatically, by just select one more field, why?
N¬ë,j°jËkj{ zºÞw
«k©oz»¢z
¦ºxj×úèThanks for reply.
- Original Message -
From: Ludwig Pummer [EMAIL PROTECTED]
To: Oscar Yen [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, February 19, 2004 8:39 AM
Subject: Re: Similar simple query slow down dramatically, by just select one more
field, why?
*This message was transferred with a trial version of CommuniGate(tm) Pro*
Oscar Yen wrote:
create table a (
imgid int not null,
parent int,
imgtype char(3),
img longtext,
primary key (imgid),
key searchkey (parent, imgid)
) type = innodb;
T1) select imgid, parent from a where parent = 10;
returns 3357 rows in 0.08 sec.
T2) select imgid, parent, imgtype from a where parent = 10;
return 3357 rows in 8.32 sec.!!!
T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in
almost same speed.
Anybody can explain my questions:
Q1- What cause the slow down, T2 vs T1 and T4 vs T3?
Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance
of T6 and T4?
Best Reguards.
Q1:
It's explained in the MySQL Documentation under Optimization - How MySQL
Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay
close attention to the 5th bullet. This explains the behavior you see
from T7.
Yes, after alter index, the mysql will use index to speedup search.
But, what caused the mysql refuse to use index searchkey in question T2/T4,
even if you explicit specify the index you want by re-write the sql like following?
T2) select imgid, parent, imgtype from a force index(searchkey) where parent = 10;
It is clear that non-indexed column imgtype does not contained in where clause!!!,
Is there any method that I can PERSUADE mysql to use index searchkey?
Q2:
I have no answer for you there, Sorry.
That's OK, let's change the expression of poor handle:
Am I right to assume mysql will store BLOB data along with other columns, when ever
mysql need to fetch rows, it will read WHOLE row, including BLOB data from the disk,
even if the BLOB data will NOT be used during query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I can't figure out what I thought would be a simple query..
Okay - I can't figure this out as a single sql statement. However Assuming there aren't likely to be duplicate rows you could do: create table temp2 (table definition) select max(endtime), non_unique_id temp1 group by non_unique_id; then you could select a.endtime, a.need_id, b.unique_id from table temp1 a, temp2 b where a.endtime=b.endtime and a.unique_id=b.unique_id; If there are duplicates I suspect you'd get away with distinct? Of course I'm sure there's a smarter way! Rgds Mike (--traitorous Oracle Ingres DBA) -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 22:30 To: Jim Matzdorff; MySQL List Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list
I can't figure out what I thought would be a simple query..
All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I can't figure out what I thought would be a simple query..
I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- 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: I can't figure out what I thought would be a simple query..
Hi guys, Have you seen the manual page for The Rows Holding the Group-wise Maximum of a Certain Field: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html I think that's what you want to do. You can either use another temporay table, the MAX-CONCAT trick, or the LEFT JOIN ... IS NULL trick from the comment on March 16, 2003. In MySQL 4.1, you could also use a subselect. Keep in mind with the LEFT JOIN ... IS NULL trick, the more duplicate values you have on your non-unique column, the more inefficient it will be. However, it seems like the subselect method would have this problem also. Can someone tell me if this is true or am I thinking wrong? Hmm. Hope that helps. Matt - Original Message - From: Larry Brown Sent: Monday, October 27, 2003 4:29 PM Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff Sent: Monday, October 27, 2003 4:37 PM Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld consumes 1.3Gb of swap for simple query on solaris
Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: sw-bug Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 43 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.004 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/gcc3.3/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/gcc3.3/bin/../lib/gcc-lib/sparc-sun-solaris2.8/3.3/specs Configured with: ../gcc-3.3/configure --prefix=/home/kilsdonk/gcc3.3/install Thread model: posix gcc version 3.3 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /usr/lib/libc.so.1 Configure command: ./configure '--without-docs' '--enable-thread-safe-client' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld consumes 1.3Gb of swap for simple query on solaris
Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 hour 17 min 38 sec Threads: 2 Questions: 12 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.003 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld consumes 1.3Gb of swap for simple query on solaris
Hi Tom, Sounds odd... Do other queries that behave normally use GROUP BY or DISTINCT? What are your configuration variables? e.g. SHOW VARIABLES or mysqladmin variables. Is sort_buffer_size set to some huge value? Matt - Original Message - From: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 5:19 PM Subject: mysqld consumes 1.3Gb of swap for simple query on solaris Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: sw-bug Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 43 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.004 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/gcc3.3/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/gcc3.3/bin/../lib/gcc-lib/sparc-sun-solaris2.8/3.3/specs Configured with: ../gcc-3.3/configure --prefix=/home/kilsdonk/gcc3.3/install Thread model: posix gcc version 3.3 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /usr/lib
Re: Re: Simple query help
El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
* Boris Villazon El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? You wanted tha latest date, right? You should use what Daniel suggested, exept you probably want ...ORDER BY date DESC LIMIT 1 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query
I use a very good book on SQL commands that was recommended in a class I took. Its generic SQL but its helped me a great deal with MySql commands. it called Teach Yourself SQL In 10 Minutes by Ben Forta. Each chapter should take you about 10 minutes to read. Its a SAMS book and very inexpensive.
Re: simple query
* [EMAIL PROTECTED] I tried the manual but cant make sense of it. How can we use wildcards on selects? select * from phrases where ph like '%who%'; -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple query
Try SELECT * FROM `phrases` WHERE ph like %who%; The percent symbol is the wildcard character for mysql queries HTH -Original Message- From: Pag [mailto:[EMAIL PROTECTED] Sent: Monday, August 25, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: simple query Imagine i have a table phrases with a field ph with contents like these: - who was it - who wasnt it - no i didnt - yes i was - dont know who I want to make a SELECT that gives me only the entries that have the word who: Something like SELECT * FROM `phrases` WHERE ph=who*; I tried the manual but cant make sense of it. How can we use wildcards on selects? Thanks Pag -- 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: simple query
* Pag Imagine i have a table phrases with a field ph with contents like these: - who was it - who wasnt it - no i didnt - yes i was - dont know who I want to make a SELECT that gives me only the entries that have the word who: Something like SELECT * FROM `phrases` WHERE ph=who*; I tried the manual but cant make sense of it. How can we use wildcards on selects? See the LIKE operator: URL: http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1234 URL: http://www.mysql.com/doc/en/MySQL_indexes.html#IDX905 SELECT * FROM `phrases` WHERE ph LIKE who%; However, this would not return your last example, dont know who. To match any occurences of who, you can use this: SELECT * FROM `phrases` WHERE ph LIKE %who%; Note that an index can not be used in this case, making it slower when you have a lot of data. Another problem is words containing other words: the last SELECT statement would also match knowhow. To only match the word who, you could try something like this: SELECT * FROM `phrases` WHERE ph = who OR ph LIKE who % OR ph LIKE % who OR ph LIKE % who %; This statement would however not find this value: who, if any. You could take a look at regular expressions: URL: http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1239 And finally, the FULLTEXT feature could possibly be of use for you: URL: http://www.mysql.com/doc/en/Fulltext_Search.html HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aborted Result in simple Query
Hello Shane, I found nothing better than the standard MySQL C API standard with MySQL. That's true. I read it now i find it very easy in comparison to the C++ documents. This one reminds me of PHP functions a lot. I have made 2 such classes, which are quite simple, not a significant ammount of error checking, or functionality, but ResultSet's and Queries can be made. If you are interested in my classes, I'd be happy to pack them up for you to That whould be very kind of you mate. Allthough i tried to use the C-API now, i'm still getting some errors, that dont now know of why they occure. It whould be alot easier to one complete example to get a glue of how to start in C/C++. use, I only ask the headers be kept in the code. if you wish so, of course. Thanks a lot, Maik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aborted Result in simple Query
Hi all,
this _my_ simple problem: I wrote a little c++ programm in linux using
the plusplus api
My purpose was to create a simple interface to mysql. Programmer uses my
class, gives a sql-string and gets a pointer to the result array wich my
class creates out of the result set.
this is a shortened part of my scripts:
[...]
Query* q = new Query(conn);
[...]
bool myConn::runQuery(char *sql)
{
(*q) sql;
cout q-preview() endl;
cout one endl;
Result res = q-store();
cout two endl;
[...]
}
and the following is the output:
[...]
SELECT name FROM test;
one
Aborted
...that's it.
If I try to get out what's wrong by
try{ [...] }
catch(BadQuery er) { cout er.error; }
i'll only receive cryptic symbols (like a misplaced pointer)
So here are my questions:
1. Is there any way to get out correctly what's wrong?
2. Is there any good(!) description of the mySQL C++ API?
(http://www.mysql.com/documentation/mysql++/index.html is horrific!)
Best regards
M. Bader
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborted Result in simple Query
Hello Mr Bader,
A simple answer to this solution, after all my own searches for a good
API to communicate with MySQL, I found nothing better than the standard
MySQL C API standard with MySQL. The calls are not at all cryptic, the
functionality is quite featureful (including escaping strings for mysql,
bless the MySQL team for adding something so simple) while having less
overhead than most of the C++ API's I've seen. And in truth, it's not
difficult to write a couple little classes which can encapsulate simple
functionality quite easily. I have made 2 such classes, which are quite
simple, not a significant ammount of error checking, or functionality, but
ResultSet's and Queries can be made.
If you are interested in my classes, I'd be happy to pack them up for you to
use, I only ask the headers be kept in the code.
My final jump onto the C API, was the extended documentation. The MySQL
team has documented it on the website very well, and you don't need to
install anything extra for the library, since it comes with MySQL.
Hope that helps,
-Shane
- Original Message -
From: M. Bader [EMAIL PROTECTED]
To: mysql Mailing Liste [EMAIL PROTECTED]
Sent: Monday, June 16, 2003 2:54 PM
Subject: Aborted Result in simple Query
Hi all,
this _my_ simple problem: I wrote a little c++ programm in linux using
the plusplus api
My purpose was to create a simple interface to mysql. Programmer uses my
class, gives a sql-string and gets a pointer to the result array wich my
class creates out of the result set.
this is a shortened part of my scripts:
[...]
Query* q = new Query(conn);
[...]
bool myConn::runQuery(char *sql)
{
(*q) sql;
cout q-preview() endl;
cout one endl;
Result res = q-store();
cout two endl;
[...]
}
and the following is the output:
[...]
SELECT name FROM test;
one
Aborted
...that's it.
If I try to get out what's wrong by
try{ [...] }
catch(BadQuery er) { cout er.error; }
i'll only receive cryptic symbols (like a misplaced pointer)
So here are my questions:
1. Is there any way to get out correctly what's wrong?
2. Is there any good(!) description of the mySQL C++ API?
(http://www.mysql.com/documentation/mysql++/index.html is horrific!)
Best regards
M. Bader
--
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: simple query
At 17:21 + 3/8/03, Andrew wrote:
come on guys I nedsome help here! MySQL Rocks
I have a set of drop downs I want to determine the dropdown menu by query?
I have:
?
require(connection.php);
mysql_connect($DBHost, $DBUser, $DBPass) or
die(could not connect);
mysql_select_db($DBName);
echo select name=\CountyID\ size=\1\ class='menuForm';
$result=mysql_query(SELECT County, CountyID FROM county ORDER BY County);
while ($row = mysql_fetch_array($result))
{
$county_id=$row['CountyID'];
$county=$row['County'];
echo option value=\$county_id\ $county /option;
}
echo /select;
?
which takes us to:
?
echo select name=\CityID\ size=\1\ class='menuForm';
$result=mysql_query(SELECT City, CityID FROM city ORDER BY City);
while ($row = mysql_fetch_array($result))
{
$city_id=$row['CityID'];
$city=$row['City'];
echo option value=\$city_id\ $city /option;
}
echo /select;
?
br /
?
but I want the city selction to be only where it is associated with
the county?
Which means what, exactly? You want only those cities to be displayed
that are located in the currently-selected county? If so, that's not
a MySQL question at all. It's a question of client-side programming, for
example, using JavaScript.
Or do you mean something else?
#
# Table structure for table `city`
#
CREATE TABLE city (
CountyID int(10) unsigned NOT NULL default '0',
City varchar(100) NOT NULL default '',
CityID bigint(20) NOT NULL auto_increment,
PRIMARY KEY (CityID),
KEY CountyID (CountyID)
);
Andrew
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A Simple Query! - SOLVED
Working solution Bruce Feist SELECT Films.idFilm, title FROM Films LEFT JOIN Loans ON (Films.idFilm = Loans.idFilm) AND (Loans.return_date IS NULL) WHERE Loans.idFilm IS NULL; Note: This solution works for me as it considers that I give no value (NULL) to the attribute return_date at the moment of borrowing a film to someone. I only give this attribute a value when the film is returned. Thanks a lot for proving me that my question has a solution! Finally Ill be able to make my program work correctly! Additionally, I will be able to prove to my Professor that MySQL has one less limitation. However, honestly I dont understand why this works, and that bothers me. If it isnt asking too much, I appreciate it if you could explain me how LEFT JOIN works and helps me to produce the result I wanted. As I said, I am new to MySQL. Actually, my only experience with databases is on a strictly theoretical level. Now I am trying to use my knowledge to create an application for my own using MySQL and PHP, which I know is a popular combination! -- Remi André Mikalsen Homepage - http://mikalsen.no.sapo.pt Email - [EMAIL PROTECTED] PS! Uttam; your solution shows me which films were on loan at least once Sam Funk; your solution shows me which films are currently on loan Thank you anyway!
RE: A Simple Query!
Hi Bruce, yep, u r right. Anyway, MySQL query optimizer will take care of it ;) regds, -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 04:00 To: [EMAIL PROTECTED] Subject: Re: A Simple Query! Hello, Uttam; We can simplify this, actually... any time L.idFilm is NULL (i.e., no row in Loans is found), L.dateReturn will have to be NULL as well. So, specifying the L.idFilm IS NULL condition is redundant. It's sufficient to look only at L.dateReturn IS NULL in the WHERE clause. Yours is an improvement over my original, though, in that it's correct g. Mine would have missed films having Loans with non-NULL dateReturns. Uttam wrote: here's my version of the answer: SELECT F.idFilm, F.title FROM Films F LEFT JOIN Loans L ON F.idFilm = L.idFilm WHERE (L.idFilm IS NULL) OR (L.dateReutrn IS NULL) ; regds, -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 08:13 select F.idFilm, F.title from Films F left join Loans L on F.idFilm = L.idFilm and L.dateReturn IS NULL where L.idFilm IS NULL; Remi Mikalsen wrote: Scheme: Films (idFilm, title) Loans (idFilm, dateLoan, dateReturn, idContact, idLoan) Contacts (idContact, name) Question: What films aren't on loan right now? Using the SQL standards it should be possible to write the following query. SELECT F.idFilm, F.title FROM Films F WHERE F.idFilm NOT IN ( SELECT L.idFilm FROM Loans L WHERE L.dateReturn IS NULL) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
