Re: Simple Query Question

[Stefan Kuhn]

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

[Willy Mularto]

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

Re: Simple query takes forever

[René Fournier]

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

[mos]

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

[Aleksandar Bradaric]

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

[Ian]

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

[walter harms]

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

[Perrin Harkins]

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: Simple Query

[Peter Brawley]

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
  

Re: simple query

[Diego Ignacio Wald]

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]

Re: simple query on an indexed col in big table is extremely slow

[SGreen]

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

[Zhe Wang]

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 of the 

Re: Simple query? Is it possible?

[Roger Baklund]

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: Simple query question

[Roger Baklund]

* 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]

Re: 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?

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

[Roger Baklund]

* 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

[Daniel Clark]

 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

[AMEVANS]

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

[Jon Haugsand]

* [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

[Allen Weeks]

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

[Roger Baklund]

* 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: simple query

[Paul DuBois]

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

Re: simple query turned ugly

[Roger Baklund]

* Vince LaMonica
[...]
 I'm currently attempting this by doing [only trying priority 1 and 2
 right now]:

 SELECT
 papers.id,
 a1.last_name as auth1,
 a2.last_name as auth2,
 papers.year
 FROM
 paper_authors, papers,
 authors AS a1
 left join authors AS a2 ON  (a2.id = paper_authors.author_id
 AND
 paper_authors.paper_id = papers.id AND paper_authors.priority = '2')
 WHERE
 a1.id  = paper_authors.author_id
 AND paper_authors.priority = '1'
 AND paper_authors.paper_id = papers.id
 ORDER BY
 year ASC

This is wrong, because paper_authors.priority = '1' in the where clause
will prevent you from finding any paper_authors with priority = '2'. You
must put this criteria in the ON clause of the LEFT JOIN:

SELECT papers.id,
  a1.last_name as auth1,
  a2.last_name as auth2,
  papers.year
FROM papers
LEFT JOIN paper_authors pa1 ON
  pa1.paper_id = papers.id AND pa1.priority = 1
LEFT JOIN authors a1 ON
  pa1.author_id = a1.id
LEFT JOIN paper_authors pa2 ON
  pa2.paper_id = papers.id AND pa2.priority = 2
LEFT JOIN authors a2 ON
  pa2.author_id = a2.id
ORDER BY year ASC

Another tip: If you rename autors.id to autors.author_id and papers.id to
papers.paper_id, you can use NATURAL JOIN, which makes things a bit simpler:

SELECT papers.id, last_name, priority, papers.year
FROM papers
NATURAL JOIN paper_authors
NATURAL JOIN authors
WHERE priority IN (1,2)
ORDER BY year ASC

(This is a different query: it returns all paper/author pairs matching the
WHERE clause (i.e., priority 1 or 2), one author for each row in the result
set, while the first query returns two authors in one row for each paper.)

The 'NATURAL JOIN' is described like this in the manual:

   * The `NATURAL [LEFT] JOIN' of two tables is defined to be
 semantically equivalent to a `INNER JOIN' or a `LEFT JOIN' with a
 `USING' clause that names all columns that exist in both tables.


HTH,

--
Roger
sql


-
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

Re: simple query turned ugly

[Roger Baklund]

* Vince LaMonica
[...]
 The authors table has 4 cols: id [primary/smallint/autoincrement],
 last_name, middle_name, first_name.

 The publications table has several cols, but the ones most important to
 this question are: id [primary/smallint/autoincrement], author1
 [smallint,
 foreign key to authors.id/default NULL], author2 [same], author3 [same],
 author4 [same], author5 [same], and year [char(4)].

 The user has created multiple author cols in the publications table
 because the order of the author matters [eg: it is better for someone to
 be an author1 than an author2 or a dreaded author5]. Some publications
 have 1 author, some have up to five.

hm... This is not good db design... I would use a third table:
'paper_authors' with the columns 'paper_id', 'author_id' and 'prio', where
'prio' is a tinyint with the values 1-5.

 Putting together a simple query to find out the names of the
 author[s] for each publication:

 SELECT author1, author2, author3, author4, author5, year
 FROM `papers` GROUP BY papers.id ORDER BY `year` ASC

 This produces a nice 'table' of each publication's 1-5 authors, listed by
 their id.

 How do I alter the query to replace their id with
 authors.last_name?

You need to JOIN with the authors table five times:

SELECT papers.id,
  concat(a1.first_name,' ',a1.middle_name,' ',a1.last_name) as author1,
  concat(a2.first_name,' ',a2.middle_name,' ',a2.last_name) as author2,
  concat(a3.first_name,' ',a3.middle_name,' ',a3.last_name) as author3,
  concat(a4.first_name,' ',a4.middle_name,' ',a4.last_name) as author4,
  concat(a5.first_name,' ',a5.middle_name,' ',a5.last_name) as author5,
  year
FROM papers
  LEFT JOIN authors a1 ON a1.id = papers.author1
  LEFT JOIN authors a2 ON a2.id = papers.author2
  LEFT JOIN authors a3 ON a3.id = papers.author3
  LEFT JOIN authors a4 ON a4.id = papers.author4
  LEFT JOIN authors a5 ON a5.id = papers.author5
ORDER BY year ASC

(Don't know why you would use GROUP BY in this case.)

LEFT JOIN is used because not all papers have five authors. Read more about
the different types of JOIN in the manual: URL:
http://www.mysql.com/doc/en/JOIN.html 

HTH,

--
Roger


-
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

RE: simple query turned ugly

[Jon Frisby]

 SELECT papers.id,
   concat(a1.first_name,' ',a1.middle_name,' ',a1.last_name) 
 as author1,
   concat(a2.first_name,' ',a2.middle_name,' ',a2.last_name) 
 as author2,
   concat(a3.first_name,' ',a3.middle_name,' ',a3.last_name) 
 as author3,
   concat(a4.first_name,' ',a4.middle_name,' ',a4.last_name) 
 as author4,
   concat(a5.first_name,' ',a5.middle_name,' ',a5.last_name) 
 as author5,
   year
 FROM papers
   LEFT JOIN authors a1 ON a1.id = papers.author1
   LEFT JOIN authors a2 ON a2.id = papers.author2
   LEFT JOIN authors a3 ON a3.id = papers.author3
   LEFT JOIN authors a4 ON a4.id = papers.author4
   LEFT JOIN authors a5 ON a5.id = papers.author5
 ORDER BY year ASC
 
 (Don't know why you would use GROUP BY in this case.)
 
 LEFT JOIN is used because not all papers have five authors. 
 Read more about
 the different types of JOIN in the manual: URL:
 http://www.mysql.com/doc/en/JOIN.html 

Purely as an informational point, this should work as well:

...
FROM 
  papers, 
  authors AS a1
  LEFT JOIN authors AS a2 ON a2.id = papers.author2
  LEFT JOIN authors AS a3 ON a3.id = papers.author3
  LEFT JOIN authors AS a4 ON a4.id = papers.author4
  LEFT JOIN authors AS a5 ON a5.id = papers.author5
WHERE
  a1.id = papers.author1
ORDER BY 
  year ASC


Note that the only difference is that the first LEFT JOIN has been
replaced with a plain JOIN.  I have no idea if this would be more
efficient in practice but it should make the nature of the data a tiny
bit clearer -- a publication presumably must have at least one author.
:)

-JF



-
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

Re: simple query turned ugly

[Vince LaMonica]

On Thu, 14 Nov 2002, Roger Baklund wrote:

} * Vince LaMonica
[snip]
}  The user has created multiple author cols in the publications table
}  because the order of the author matters [eg: it is better for someone to
}  be an author1 than an author2 or a dreaded author5]. Some publications
}  have 1 author, some have up to five.
} 
} hm... This is not good db design... I would use a third table:
} 'paper_authors' with the columns 'paper_id', 'author_id' and 'prio', where
} 'prio' is a tinyint with the values 1-5.

You are correct, thanks. I added a paper_authors table with the above cols 
[except I called 'prio' 'priority' instead.

Applying your left join [as well as Jon Frisby's suggestion] works great 
for the non-normalized version of this small database. But now that I have 
the 'join table' [paper_authors] above, I'm not so certain how to produce 
the same results.

I'm currently attempting this by doing [only trying priority 1 and 2 
right now]:

SELECT
papers.id,
a1.last_name as auth1,
a2.last_name as auth2,
papers.year
FROM 
paper_authors, papers,
authors AS a1
left join authors AS a2 ON  (a2.id = paper_authors.author_id
AND 
paper_authors.paper_id = papers.id AND paper_authors.priority = '2')
WHERE
a1.id  = paper_authors.author_id
AND paper_authors.priority = '1'
AND paper_authors.paper_id = papers.id
ORDER BY
year ASC

Here's the 3 tables:

mysql desc authors;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| id  | smallint(3) |  | PRI | NULL| auto_increment |
| last_name   | varchar(60) |  | | ||
| first_name  | varchar(60) | YES  | | NULL||
| middle_name | varchar(60) | YES  | | NULL||
+-+-+--+-+-++

mysql desc papers ;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| id  | smallint(3) unsigned |  | PRI | NULL| auto_increment |
| year| varchar(4)   |  | | ||
| title   | varchar(255) | YES  | | NULL||
[snip]
[i took the author1 - author5 cols out]

mysql desc paper_authors;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| paper_id  | smallint(3) unsigned |  | | 0   |   |
| author_id | smallint(3) unsigned |  | | 0   |   |
| priority  | tinyint(1) unsigned  |  | | 0   |   |
+---+--+--+-+-+---+

Running the above query produces a the correct priority 1 author, but the 
priority 2 author rows are all NULL. Running the query with all 4 left 
joins results in the same NULL values in the auth2, auth3, etc, cols. I've 
got to be missing something basic here, right? 

Any further assistance would be most appreciated. 

/vjl/

-- 
Vince LaMonica   UC Irvine,  School  of  Social Ecology
 W3 Developer   *  116 Social Ecology I, Irvine, CA 92697
 [EMAIL PROTECTED]  http://www.seweb.uci.edu/techsupport

 Tower: Delta Zulu Romeo, turn right now and report your heading.
 Pilot: Wilco. 341, 342, 343, 344, 345...


-
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

RE: simple query question

[Meidling, Keith, CTR, OSD-C3I]

I've seen a lot about 'InnoDB'. What is it?

-Original Message-
From: Weaver, Walt [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 12, 2002 4:09 PM
To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: simple query question


I'd use the InnoDB table type and establish a primary key/foreign key
relationship (parent-child) between the two. That way referential integrity
will be done for you by the database.

--Walt Weaver
  Bozeman, Montana

-Original Message-
From: Chris Burger [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 12, 2002 1:57 PM
To: [EMAIL PROTECTED]
Subject: simple query question


I have I hope a simple query question.

 

I have two tables

 1 One has customer id and a store id and transaction information

 2 Second has a store id and store information

 

There is multiple transactions in the first table,  however there is
only one record for each store id in the second table.  What I need to
make sure is that for every store id in the first table I have a
corresponding store id record in the second table?


 

Any suggestions would be appreciated.

 

Chris Burger



-
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

-
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

-
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

RE: simple query question

[Iikka Meriläinen]

See http://www.innodb.com

Regards,
Iikka

ps. The MySQL manual also offers some rough guidelines on where InnoDB
excels.

On Fri, 13 Sep 2002, Meidling, Keith, CTR, OSD-C3I wrote:

 I've seen a lot about 'InnoDB'. What is it?

 -Original Message-
 From: Weaver, Walt [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 12, 2002 4:09 PM
 To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
 Subject: RE: simple query question


 I'd use the InnoDB table type and establish a primary key/foreign key
 relationship (parent-child) between the two. That way referential integrity
 will be done for you by the database.

 --Walt Weaver
   Bozeman, Montana

 -Original Message-
 From: Chris Burger [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 12, 2002 1:57 PM
 To: [EMAIL PROTECTED]
 Subject: simple query question


 I have I hope a simple query question.



 I have two tables

  1 One has customer id and a store id and transaction information

  2 Second has a store id and store information



 There is multiple transactions in the first table,  however there is
 only one record for each store id in the second table.  What I need to
 make sure is that for every store id in the first table I have a
 corresponding store id record in the second table?




 Any suggestions would be appreciated.



 Chris Burger



 -
 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

 -
 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

 -
 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


**
* Iikka Meriläinen   *
* E-mail: [EMAIL PROTECTED] *
* Vaala, Finland *
**


-
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

RE: simple query question

[Weaver, Walt]

I'd use the InnoDB table type and establish a primary key/foreign key
relationship (parent-child) between the two. That way referential integrity
will be done for you by the database.

--Walt Weaver
  Bozeman, Montana

-Original Message-
From: Chris Burger [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 12, 2002 1:57 PM
To: [EMAIL PROTECTED]
Subject: simple query question


I have I hope a simple query question.

 

I have two tables

 1 One has customer id and a store id and transaction information

 2 Second has a store id and store information

 

There is multiple transactions in the first table,  however there is
only one record for each store id in the second table.  What I need to
make sure is that for every store id in the first table I have a
corresponding store id record in the second table?


 

Any suggestions would be appreciated.

 

Chris Burger



-
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

-
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

Re: Simple Query

[Benjamin Pflugmann]

Hi.

Please, please always cite error message if you get any. Most time
they will be instant clue to some list members and without them it is
almost always the start of guessing time.

That said, it sounds as if the another user does not have the file
privilege to access the UNIX socket (that .sock file), which is needed
to make the connection to the MySQL server. For more info, read
http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html

As intermediate solution you can try to connect via TCP sockets by
giving an hostname/IP. Something like

mysql -h 127.0.0.1

Bye,

Benjamin.


On Thu, May 30, 2002 at 10:47:13PM -0700, [EMAIL PROTECTED] wrote:
 Hi There
 This Is Pragneshkumar Gandhi From India
 I am New To This Mailing List.
 I have one problem
 Yesterday i installed mysql  in my linux box.
 and it is working fine with root login.
 i installed mysql with root login.
 but if i login as another user
 and on typing mysql on $ promt. it is giving some error of
 .sock file
 can anybosy guide me.
 thanks
 nJoy week-end

-- 
[EMAIL PROTECTED]

-
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

RE: Simple Query

[Quentin Bennett]

SELECT * FROM tableA left join tableb on tablea.prod_id = tableb.prod_id
where tableb.prod_id is NULL;

-Original Message-
From: BD [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 15 February 2001 15:16
To: [EMAIL PROTECTED]
Subject: Simple Query


For a database cleanup project...

Table A: prod_id, prod_desc
Table B: prod_id, prod_price, (and various other fields)

Would like to come up with a query that will give me a list of all rows in
Table A that don't exist in Table B... I've tried several different ways,
but can't seem to come up with one that works...?

TIA


-
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

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
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

Re: Simple Query

[BD]

Perfect!
Thanks...

http://www.bustdustr.net
Home Of Radio Free BD


 SELECT * FROM tableA left join tableb on tablea.prod_id = tableb.prod_id
 where tableb.prod_id is NULL;

 -Original Message-
 From: BD [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, 15 February 2001 15:16
 To: [EMAIL PROTECTED]
 Subject: Simple Query


 For a database cleanup project...

 Table A: prod_id, prod_desc
 Table B: prod_id, prod_price, (and various other fields)

 Would like to come up with a query that will give me a list of all rows in
 Table A that don't exist in Table B... I've tried several different ways,
 but can't seem to come up with one that works...?

 TIA



-
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