Re: How to dynamically create database and tables on mysql?
What is your application? Maybe we can help more. On 2011-10-21, at 2:05 AM, 王科选 wrote: Hi, Is there any way to dynamically create database and tables on mysql? For example, if I want to create 100 databases(dbname is unknown until run time), with 100 predefined tables in it, how to achieve that? Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=m...@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
MySQL anemic GIS support
Anyone have any idea on if/when MySQL will get real GIS support? http://mysqldbnews.blogspot.com/2007/10/does-mysql-gis-make-grade.html …is what I'm referring to. Specifically, the factor that many functions are quietly replaced with MBRContains(). This makes it, for example, not possible to determine with certainty (in SQL) if a point lies within a non-rectangular polygon. I ask because I'm looking at moving a big part of our applications to Postgresql, and, well, I'd rather not have to. …Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simple query takes forever
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=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: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same
queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing
this?
On 2009-12-28, at 1:19 PM, René Fournier wrote:
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254
-114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'),
coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...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: Why does this query take so long?
I think you might be right. The good-to-poor performance I'm seeing is so
intermittent. And I see now that it's also with other queries, though not as
extremely obvious as the spatial queries. However, even if the Index can't fit
in memory (4GB of RAM, lots free), just reading it from disk should allow
sub-millisecond response, no?
Strange thing is that I've used my laptop for benchmarking for the past five
years and it's always produced results fairly typical or at least consistent in
relation to our servers. This new thing is... new.
On 2009-12-29, at 3:18 AM, Gavin Towey wrote:
It sounds like your laptop might be paging mysql's memory to disk or
something like that. Your laptop may not be the most reliable source for
benchmarks.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Monday, December 28, 2009 2:16 AM
To: René Fournier
Cc: mysql
Subject: Re: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same
queries are taking 3-10 seconds instead of 0.01 seconds. What could be
causing this?
On 2009-12-28, at 1:19 PM, René Fournier wrote:
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254
-114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'),
coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 |
NULL | 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE
Why does this query take so long?
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589
-114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL |
5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...REne
Re: Why does this query take so long?
So just to clarify (hello?), the index which *should* be used (EXPLAIN says so)
and *should* make the query run faster than 4 seconds either isn't used (why?)
or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL |
5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589
-114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL |
5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589
-114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...REne
--
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 does this query take so long?
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and
now the spatial queries run fast.
Has anyone seen this sort of thing happen? Maybe the Index got corrupted
somehow, and then MySQL had to do a full table scan (even though EXPLAIN
indicated it would use the Spatial Index)?
On 2009-12-28, at 9:28 AM, René Fournier wrote:
So just to clarify (hello?), the index which *should* be used (EXPLAIN says
so) and *should* make the query run faster than 4 seconds either isn't used
(why?) or simply doesn't speed up the query (again, why?).
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL |
5260 | Using where |
++-+---+---+---+---+-+--+--+-+
SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589
-114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
8 rows in set (3.87 sec)
On 2009-12-27, at 3:59 PM, René Fournier wrote:
So... there is an index, and it's supposedly used:
mysql EXPLAIN SELECT id, province, latitude, longitude,
AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates);
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+---+-+--+--+-+
| 1 | SIMPLE | qs| range | coord | coord | 27 | NULL
| 5260 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)
But when I run the query:
mysql SELECT id, province, latitude, longitude, AsText(coordinates),
s_ts_r_m, quartersection FROM qs WHERE
MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254
-114.82248918,51.65126254 -114.78150333,51.62582589
-114.78150333,51.62582589 -114.82248918))'), coordinates)
- ;
++--+-+---+--+--++
| id | province | latitude| longitude | AsText(coordinates)
| s_ts_r_m | quartersection |
++--+-+---+--+--++
| 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228
-114.79282412) | 04-031-06 W5 | N4 |
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112
-114.79283278) | 09-031-06 W5 | N4 |
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789
-114.81645649) | 05-031-06 W5 | N4 |
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119
-114.81643801) | 08-031-06 W5 | N4 |
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568
-114.80475858) | 05-031-06 W5 | E4 |
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028
-114.80479925) | 05-031-06 W5 | NE |
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442
-114.80478262) | 08-031-06 W5 | E4 |
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854
-114.80476596) | 08-031-06 W5 | NE |
++--+-+---+--+--++
8 rows in set (3.87 sec)
So, there are ~2.6 million rows in the table, and coordinates is
spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing
wrong?
...REne
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Spatial extensions
Hi Gavin,
OK, I finally tracked down the source of the trouble... My CREATE TABLE query
included a regular KEY index instead of SPATIAL KEY -- that's why the
MBRCONTAINS wasn't working... Works now!!
Thanks for your help.
On 2009-12-22, at 2:42 AM, Gavin Towey wrote:
How did you populate the point column? Should be something like:
UPDATE places SET point= =GeomFromText(CONCAT(Point(,longitude,
,latitude,)));
You also should have a spatial index:
ALTER TABLE places MODIFY coordinates point NOT NULL, add spatial index
(coordinates);
You can check the data with the ASTEXT() function:
SELECT longitude, latitude, ASTEXT(coordinates) FROM places;
Hope this helps
-Gavin Towey
From: René Fournier [mailto:m...@renefournier.com]
Sent: Saturday, December 19, 2009 12:42 AM
To: Gavin Towey
Cc: mysql
Subject: Re: Spatial extensions
Thanks Gavin.
I've got part your query working... sort of. Something I can't figure out is
how to use MBRContains on a table with a POINT column. For example, this
works:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), GeomFromText('Point(49 -114)')) limit 10;
++--+
| id | astext(coordinates) |
++--+
| 1 | POINT(49.00701238 -110.00507933) |
| 2 | POINT(49.01415809 -110.01615511) |
| 3 | POINT(49.01424023 -110.00508075) |
| 4 | POINT(48.99978158 -110.01617366) |
| 5 | POINT(48.99978996 -110.00507794) |
| 6 | POINT(49.00683419 -110.02751996) |
| 7 | POINT(49.01402057 -110.03861578) |
| 8 | POINT(49.01407281 -110.02750442) |
| 9 | POINT(48.99974667 -110.0386263) |
| 10 | POINT(48.9997718 -110.0275421) |
++--+
10 rows in set (0.00 sec)
But when I try to use the table's POINT column, nothing is returned:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), (coordinates)) limit 10;
Empty set (0.00 sec)
What am I missing? For clarity, here's the table schema:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`),
KEY `coord` (`coordinates`(25))
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
On 2009-12-18, at 2:21 AM, Gavin Towey wrote:
Not only is it 5.1, but there's a special branch that has improved GIS
functions not found in the regular MySQL. I'm not sure if/when they're
planning on rolling them back into mysql:
http://downloads.mysql.com/forge/mysql-5.1.35-gis/
If it's not possible to use that version, then you can still implement a
Distance function yourself as a stored procedure or UDF. Just google for
mysql+haversine or something similar.
The important part though is the MBRContains, which does an efficient box
cull and uses the spatial index. Oops, I forgot to change a couple
occurances of line_segment to coordinates line_segment was just the
column name I was using in my original query.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Thursday, December 17, 2009 8:54 AM
To: Gavin Towey
Cc: mysql
Subject: Re: Spatial extensions
Awesome, this is what I was trying to find, as you succinctly wrote it. I
*really* appreciate getting pointed in the right direction, since I haven't
found a lot of MySQL's GIS tutorials directed at what I'm trying to do.
Still, a couple questions, the Distance() function you included, that must
require 5.1 or higher right? 5.0.88 on my box throws an error:
Function places.Distance does not exist
Also, where does line_segment come from in the below query?
Thanks.
...Rene
On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
Yes, spatial indexes are very fast:
Query would be something like:
SET @center = GeomFromText('POINT(37.372241 -122.021671)');
SET @radius = 0.005;
SET @bbox = GeomFromText(CONCAT('POLYGON((',
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))')
);
select id, astext(coordinates), Distance(@center,line_segment) as dist
FROM places where MBRContains(@bbox, line_segment) order by dist limit 10;
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Wednesday, December 16, 2009 4:32 PM
To: mysql
Subject: Spatial extensions
I have table with 2 million rows of geographic points
Why does this query return no rows?
This query works as expected:
mysql SELECT id, AsText(coordinates) FROM qs WHERE latitude 48.6 AND
latitude 53.4 AND longitude -116.4 AND longitude -111.6 LIMIT 3;
++--+
| id | AsText(coordinates) |
++--+
| 168155 | POINT(48.99702035 -111.60886893) |
| 168154 | POINT(48.99707385 -111.61983703) |
| 168160 | POINT(48.99712759 -111.63108001) |
++--+
3 rows in set (0.00 sec)
But this one doesn't:
mysql SELECT id, AsText(coordinates) FROM qs WHERE
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), coordinates) LIMIT 3;
Empty set (0.00 sec)
What am I doing wrong?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Spatial extensions
Thanks Gavin.
I've got part your query working... sort of. Something I can't figure out is
how to use MBRContains on a table with a POINT column. For example, this works:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), GeomFromText('Point(49 -114)')) limit 10;
++--+
| id | astext(coordinates) |
++--+
| 1 | POINT(49.00701238 -110.00507933) |
| 2 | POINT(49.01415809 -110.01615511) |
| 3 | POINT(49.01424023 -110.00508075) |
| 4 | POINT(48.99978158 -110.01617366) |
| 5 | POINT(48.99978996 -110.00507794) |
| 6 | POINT(49.00683419 -110.02751996) |
| 7 | POINT(49.01402057 -110.03861578) |
| 8 | POINT(49.01407281 -110.02750442) |
| 9 | POINT(48.99974667 -110.0386263) |
| 10 | POINT(48.9997718 -110.0275421) |
++--+
10 rows in set (0.00 sec)
But when I try to use the table's POINT column, nothing is returned:
mysql select id, astext(coordinates) FROM places where
MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6
-111.6,48.6 -116.4))'), (coordinates)) limit 10;
Empty set (0.00 sec)
What am I missing? For clarity, here's the table schema:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`),
KEY `coord` (`coordinates`(25))
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
On 2009-12-18, at 2:21 AM, Gavin Towey wrote:
Not only is it 5.1, but there's a special branch that has improved GIS
functions not found in the regular MySQL. I'm not sure if/when they're
planning on rolling them back into mysql:
http://downloads.mysql.com/forge/mysql-5.1.35-gis/
If it's not possible to use that version, then you can still implement a
Distance function yourself as a stored procedure or UDF. Just google for
mysql+haversine or something similar.
The important part though is the MBRContains, which does an efficient box
cull and uses the spatial index. Oops, I forgot to change a couple
occurances of line_segment to coordinates line_segment was just the
column name I was using in my original query.
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Thursday, December 17, 2009 8:54 AM
To: Gavin Towey
Cc: mysql
Subject: Re: Spatial extensions
Awesome, this is what I was trying to find, as you succinctly wrote it. I
*really* appreciate getting pointed in the right direction, since I haven't
found a lot of MySQL's GIS tutorials directed at what I'm trying to do.
Still, a couple questions, the Distance() function you included, that must
require 5.1 or higher right? 5.0.88 on my box throws an error:
Function places.Distance does not exist
Also, where does line_segment come from in the below query?
Thanks.
...Rene
On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
Yes, spatial indexes are very fast:
Query would be something like:
SET @center = GeomFromText('POINT(37.372241 -122.021671)');
SET @radius = 0.005;
SET @bbox = GeomFromText(CONCAT('POLYGON((',
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))')
);
select id, astext(coordinates), Distance(@center,line_segment) as dist
FROM places where MBRContains(@bbox, line_segment) order by dist limit 10;
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Wednesday, December 16, 2009 4:32 PM
To: mysql
Subject: Spatial extensions
I have table with 2 million rows of geographic points (latitude, longitude).
Given a location -- say, 52º, -113.9º -- what's the fastest way to query the
10 closest points (records) from that table? Currently, I'm using a simple
two-column index to speed up queries:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
My current query is fairly quick:
SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND
52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296;
But I wonder a couple things:
1. Would MySQL's [seemingly anemic] spatial extensions would speed things up
if I added a column of type POINT (and a corresponding spatial INDEX)?
CREATE TABLE
Re: Spatial extensions
Awesome, this is what I was trying to find, as you succinctly wrote it. I
*really* appreciate getting pointed in the right direction, since I haven't
found a lot of MySQL's GIS tutorials directed at what I'm trying to do.
Still, a couple questions, the Distance() function you included, that must
require 5.1 or higher right? 5.0.88 on my box throws an error:
Function places.Distance does not exist
Also, where does line_segment come from in the below query?
Thanks.
...Rene
On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
Yes, spatial indexes are very fast:
Query would be something like:
SET @center = GeomFromText('POINT(37.372241 -122.021671)');
SET @radius = 0.005;
SET @bbox = GeomFromText(CONCAT('POLYGON((',
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))')
);
select id, astext(coordinates), Distance(@center,line_segment) as dist
FROM places where MBRContains(@bbox, line_segment) order by dist limit 10;
Regards,
Gavin Towey
-Original Message-
From: René Fournier [mailto:m...@renefournier.com]
Sent: Wednesday, December 16, 2009 4:32 PM
To: mysql
Subject: Spatial extensions
I have table with 2 million rows of geographic points (latitude, longitude).
Given a location -- say, 52º, -113.9º -- what's the fastest way to query the
10 closest points (records) from that table? Currently, I'm using a simple
two-column index to speed up queries:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
My current query is fairly quick:
SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND
52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296;
But I wonder a couple things:
1. Would MySQL's [seemingly anemic] spatial extensions would speed things up
if I added a column of type POINT (and a corresponding spatial INDEX)?
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`),
KEY `coord` (`coordinates`(25))
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
2. How would I write the query?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in
the contents of this message, which arise as a result of e-mail transmission.
[FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA,
FriendFinder.com
--
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
Spatial extensions
I have table with 2 million rows of geographic points (latitude, longitude). Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10 closest points (records) from that table? Currently, I'm using a simple two-column index to speed up queries: CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My current query is fairly quick: SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296; But I wonder a couple things: 1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a column of type POINT (and a corresponding spatial INDEX)? CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2. How would I write the query? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best practice: Finding the closest point -- POINT (51 -114)
Just wondering with MySQL 5.0, if using the spatial extensions provides any real performance advantage in the following scenario: Table with ~1 million rows: CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; Given a point, say, 51º, -114º -- what's the fastest way to select the closest 20 places from the above table? As you can see, the table already has two indexes. I'm currently using the first one, with the following query $sql = SELECT *, SQRT( POW( 69.1 * ( latitude - .$lat.) , 2 ) + POW( 69.1 * ( .$lng. - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS distance FROM lsd WHERE latitude BETWEEN .$sw[0]. AND .$ne[0]. AND longitude BETWEEN .$sw[1]. AND .$ne[1]. ORDER BY distance ASC LIMIT 0,20; (So it pre-calculate a rectangle of sorts -- select all the points within them, then sort.) But with Spatial extensions, it should be much easier, and much faster than this right? That's what I thought, except that still in 5.0 MySQL seems to lack very basic GIS operations. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqlcheck user minimum privileges
Just wondering what they are. I'd rather not use the MySQL root user for a backup script, if I can get away with MySQL user with reduced privileges, for the following command: mysqlcheck -ao --auto-repair --all-databases -u someuserotherthanroot -pPASSWORD ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PHP Warning: mysql_query(): MySQL server has gone away
I have a query... INSERT IGNORE INTO archive.history2 SELECT * FROM main.history2 ORDER BY main.history2.id ASC LIMIT 100 ...that after a certain number of records in the LIMIT clause, fails. Specifically, it fails when run at the command line (as a PHP script) with the error: PHP Warning: mysql_query(): MySQL server has gone away However, the same query executes fine when run at the mysql command prompt, or from within Sequel Pro -- can execute it with a LIMIT clause of 2 million. So, it appears I'm exhausting some MySQL limit or system resource, but before I go blindly raising values, I would like to understand which ones can cause this. About the query, it's meant to archive old records from one database table to a different database. It works fine on smaller history tables, but on bigger ones, with more than 500,000 rows, it starts to fail. What sorts of limits would this start to hit from a PHP command-line script, but not the interactive MySQL shell? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
So, as a followup, I ran mysqldump on the actual server (with the output directed over AFP to another machine on the network) -- as opposed to running mysqldump on the destination server and connecting to the databases over TCP/IP (both are running 5.1.39 PPC 64-bit). *That* dump file imported OK. So... It appears there's a bug in mysqldump that occurs intermittently when using TCP/IP connections to the MySQL database server. I haven't seen this bug occur on smaller databases, but it was pretty consistent on my non-damaged database of 9GB+. Anyway, for now I'm going to run automysqlbackup on the actual machine and simply dump to an external drive or another server on the network. On 2009-11-20, at 12:58 PM, René Fournier wrote: Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.)
MySQL 5.1.41 compatible with Mac OS X Server 10.6?
Hi, I've installed MySQL 5.1.41 x86_64 on fresh install of Mac OS X Server 10.6.2 (where the bundled version is 5.0.82. Just a couple questions: 1. Have any of your encountered compatibility issues with MySQL 5.1.x on Mac OS X Server 10.6.2? (Since I am in the process of migrating several MySQL+PHP applications over from Server 10.4.x and thought it worthwhile to ask.) 2. Installing MySQL 5.1.41 went fine -- no complaints from the installer. Now, I'm wondering how to hook-up the Server Admin MySQL Service to this newly installed MySQL instead of the bundled version. (Such that it mysqld 5.1.41 starts at bootup, and the nice move data directory feature of the Server Admin service works. Thanks! ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: i googled the problem,it says that's a mysql bug.This is the source page :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, And i suggest you ignore the error when import or correct the dump to the source database and then import again. Best wishes!
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Except that, in my case, both machines are running 5.1.39. Mac OS X PPC 64-bit. I tried importing another, different dump file of the same database, and this time mysql returned a different error: Sat Nov 21 13:16:20 -- Minas-Tirith :: mysql -u root -p dump_file.sql Enter password: ERROR 1136 (21S01) at line 6300: Column count doesn't match value count at row 6253 So, a different error. And it appears that each time mysqldump runs, the resulting file has different problem. On 2009-11-21, at 5:05 PM, Claudio Nanni wrote: There is a bug in mysqldump that prevents mysql from version 5.0.80 from importing dumps from a old version of mysqldump. I had this verified by MySQL support. You can have a look at this bug: http://bugs.mysql.com/bug.php?id=41486 Cheers Claudio 2009/11/21 René Fournier m...@renefournier.com It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: i googled the problem,it says that's a mysql bug.This is the source page :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, And i suggest you ignore the error when import or correct the dump to the source database and then import again. Best wishes! -- Claudio
Mysqldump produces bad sql on 5.1.39 OS X PPC 64-bit
So... I'm trying to export a fairly large database (~10 GB). I've done it dozens of times before, using scripts on a regular basis. Now, the database is growing, and lately, every dump file produced has problems. When I try to run mysql -u root -p dump_file.sql, MySQL will fail and return an error -- always different with each dump_file. A few recent examples (of dump files produced on different days): ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' ERROR 1136 (21S01) at line 6300: Column count doesn't match value count at row 6253 ERROR 1136 (21S01) at line 7493: Column count doesn't match value count at row 7645 When I examine the original database at the places that, in the dump file, there is a problem, the master data is fine. So, what can I try? I've posted an earlier version of this problem, and there were some good ideas, but none of them apply or have helped in my case. I increased max_allowed_packet to 256MB. I've run mysqldump on the server itself, as well as from a machine on the local network. I've exported across the network over TCP/IP and over AFP. Nothing works -- I always get an error in the mysql dump file. On the other hand, if run mysqldump on other, much smaller databases on the same server, the dump file imports fine into other MySQL instances. Ideas? Anyone, anyone? Bueller?? ...Rene
Strange problem with mysqldump / automysqlbackup (ERROR 1300)
I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.) In any case, it seems clear that during the dump, mysqldump is periodically adding a character to the dump file. On 2009-11-20, at 12:41 PM, Gavin Towey wrote: Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't backup Slave with mysqldump -- Duplicated key, damaged tables
Hi, I've spent the last few days trying to set up basic Master Slave replication with auto-backup on the Slave: Xserve G5 (10.4.11 Server) -- PowerMac G5 (10.5.8 Client) -- Both running MySQL 5.1.39 64-bit PowerPC I've followed the docs and several online articles meticulously, and got replication seemingly working -- with one problem: I can't get a valid mysqldump of the database from the Slave. 1. If I run mysqldump on the slave immediately after loading it with a snapshot of the Master (but before running start slave;), the mysqldump exports the database OK. 2. If I run mysqldump on the slave after having started synchronization (start slave;) and then stopping slave -- mysqldump doesn't work. It reports errors of duplicate keys and sometimes damaged tables. 3. If I run mysqldump on the Master, in any case, at any time, mysqldump successfully exports the database with no problems. So... The slave data is okay, until replication is started, then it's screwed up. If I run mysqlcheck -ao --auto-repair on the Slave, I get all kinds of duplicate keys and reports of damaged tables. Mysqlcheck run on the Master returns no errors. I've been reading everything I can and racking my brains, and can't figure out what I'm missing. To be clear: Before running mysqldump on the slave, I run slave stop;. And in the beginning, to get both Master and Slave in sync, I've tried both LOAD DATA FROM MASTER and running mysqldump on the master, then loading the .sql file into the Slave (having noted the binary log and position, etc. of the master already, and doing flush tables with read lock; etc., etc.) Any ideas? I would really love to be able to run replication (which I know a zillion people are all over the world, in configurations far more complex than mine) and do mysqldumps on the slave, thereby sparing a big spike on the master. I feel like the solutions is close... but still out of reach. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Ever-shrinking disk space after repeated DB restores
OK, I found the answer. The binary log files were filling up (/usr/ local/mysql/data on OS X/mysql-bin.x). Easy fix: mysql reset master; And all the disk space is recovered On 15-Mar-09, at 10:47 AM, René Fournier wrote: It seems MySQL isn't releasing disk space on my laptop after I copy a database from one machine to it. From the server, I'm running something like this: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 ...every night. And the remaining disk space on my laptop is reduced daily by the size of the database tables I'm dropping and then adding. Is there something I can do to reclaim that space? I've restarted MySQL and my computer (OS X 10.5.6) to no avail. Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Ever-shrinking disk space after repeated DB restores
It seems MySQL isn't releasing disk space on my laptop after I copy a database from one machine to it. From the server, I'm running something like this: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 ...every night. And the remaining disk space on my laptop is reduced daily by the size of the database tables I'm dropping and then adding. Is there something I can do to reclaim that space? I've restarted MySQL and my computer (OS X 10.5.6) to no avail. Thanks. ...Rene
Re: mysqlimport remote host problem
Thanks. That works great. On 10-Mar-09, at 9:36 PM, Rob Wultsch wrote: On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com wrote: OK, I've managed to do the same thing with just the mysql command line program: mysql -h 192.168.0.224 -u root -p alba2 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql Works great. However, the sql file is normally gzipped, so Can I ungzip the file on the fly (and without removing the .gzip version) and pipe the contents as I did above? (Yes, I'm UNIX-impaired.) Something like: mysql -h 192.168.0.224 -u root -p alba2 gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz But so that it works... ...Rene Pipe is your friend. You probably want something like: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqlimport remote host problem
I'm writing script that, each night, copies a small database to my
laptop on the local network. I'm having trouble getting it to work.
Here's my syntax so far (run on the server):
mysqlimport --host=192.168.0.224 --user=root --password alba2
alba2_2009-03-10_00h45m.Tuesday.sql
Which produces:
---
mysqlimport: Error: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '-03-10_00h45m IGNORE 0 LINES' at line 1, when
using table: alba2_2009-03-10_00h45m
---
The sql file is produced by automysqlbackup... Not sure what I'm
missing, probably something obvious. Anyway, here's the first part of
the sql file I want to import:
-- MySQL Administrator dump 1.4
--
-- --
-- Server version 5.0.67-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Create schema alba2
--
CREATE DATABASE IF NOT EXISTS alba2;
USE alba2;
--
-- Definition of table `alba2`.`accounts`
--
DROP TABLE IF EXISTS `alba2`.`accounts`;
CREATE TABLE `alba2`.`accounts` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`fp` varchar(40) NOT NULL,
`created` int(10) unsigned NOT NULL default '0',
`status` enum('Active','Inactive') NOT NULL default 'Active',
`account_name` varchar(40) NOT NULL,
`account_full_name` varchar(40) NOT NULL,
`address` varchar(40) NOT NULL,
`city` varchar(40) NOT NULL,
`province` varchar(10) NOT NULL,
`postcode` varchar(10) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `alba2`.`accounts`
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqlimport remote host problem
OK, I've managed to do the same thing with just the mysql command line
program:
mysql -h 192.168.0.224 -u root -p alba2 /Backup/Latest/
alba2_2009-03-10_00h45m.Tuesday.sql
Works great. However, the sql file is normally gzipped, so Can I
ungzip the file on the fly (and without removing the .gzip version)
and pipe the contents as I did above? (Yes, I'm UNIX-impaired.)
Something like:
mysql -h 192.168.0.224 -u root -p alba2 gzip -dc /Backup/Latest/
alba2_2009-03-10_00h45m.Tuesday.sql.gz
But so that it works...
...Rene
On 10-Mar-09, at 7:38 PM, René Fournier wrote:
I'm writing script that, each night, copies a small database to my
laptop on the local network. I'm having trouble getting it to work.
Here's my syntax so far (run on the server):
mysqlimport --host=192.168.0.224 --user=root --password alba2
alba2_2009-03-10_00h45m.Tuesday.sql
Which produces:
---
mysqlimport: Error: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '-03-10_00h45m IGNORE 0 LINES' at line 1, when
using table: alba2_2009-03-10_00h45m
---
The sql file is produced by automysqlbackup... Not sure what I'm
missing, probably something obvious. Anyway, here's the first part
of the sql file I want to import:
-- MySQL Administrator dump 1.4
--
-- --
-- Server version 5.0.67-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Create schema alba2
--
CREATE DATABASE IF NOT EXISTS alba2;
USE alba2;
--
-- Definition of table `alba2`.`accounts`
--
DROP TABLE IF EXISTS `alba2`.`accounts`;
CREATE TABLE `alba2`.`accounts` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`fp` varchar(40) NOT NULL,
`created` int(10) unsigned NOT NULL default '0',
`status` enum('Active','Inactive') NOT NULL default 'Active',
`account_name` varchar(40) NOT NULL,
`account_full_name` varchar(40) NOT NULL,
`address` varchar(40) NOT NULL,
`city` varchar(40) NOT NULL,
`province` varchar(10) NOT NULL,
`postcode` varchar(10) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `alba2`.`accounts`
--
--
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
Command-line PHP script, MySQL CPU usage goes sky-high, stays there--why?
Hello, I have a command-line PHP script--called Listener--that is designed to run indefinitely with a predictable CPU usage and memory footprint. In a nutshell, it's a multi-client socket server that waits for incoming connections, processes incoming data, stores results in a MySQL database, and basically gets on with its life. And it works. No errors or serious problems to speak of. And I've been running it for a couple years on an Xserve dual-G5 2GHz w/ OS X Server 10.4.11). Six months ago, the program would run for days, even a couple weeks, without a hitch. The only reason I would stop the script is for some other purpose, like a software update. However, the number of socket clients connecting in the past 3-4 months has steadily increased, and this seems to have exposed (if not created) a strange performance issue with PHP 5.2.4, MySQL 5.0.45 and/or Mac OS X Server 10.4.11. (I say and/or because I am unsure where the problem's cause really lies.) Basically, after the script has been running for a day or so (processing essentially the amount data that used to take two weeks), the CPU usage of the machine goes from 30% (normal) to 80-90%. This appears to be irrespective of the number of clients connected to the server at the time, but rather the amount of time the script has been running (and therefore cumulative cycles it's operated, data processed, MySQL queries executed, etc.). And the CPU usage stays high, even when the actual load (number of clients) decreases. At this time, if I run top, I get the following info: 22512 mysqld 91.6% 8:22:12 31 106 125 305M+ 3.20M 260M 475M 17104 php 7.2% 81:14.01 115 145 5.08M 7.80M 10.9M- 87.5M 22537 Terminal 6.6% 2:59:59 22 176 240 12.3M 21.2M 18.3M- 236M [...] At first I thought, okay, it's MySQL's fault. Maybe a lot of slow- running queries. But the slow query log is pretty clean. So maybe it's a combination of Mac OS X and MySQL and PHP? I Googled for a similar problem, and finally ran across this article: http://www.shawnhogan.com/2005/10/mysql-problems-on-mac-os-x-server.html ...where the author describes a very similar CPU usage pattern. I tried his suggested fixes and they seemed to have helped a little (or maybe it's my wishful thinking--hard to tell), since the high CPU load issue doesn't appear to happen as soon... But it still happens. Anyway, I'm really stumped as to what to do next, where to look, etc. If I stop the script, and restart it (but not the MySQL itself), CPU usage goes back to normal--for about a day or two. The only thing I thought might be connected is how many short-lived PHP child processes Listener creates--around 20-30,000 per day. Sounds higher, but on average it's just one every 2-3 seconds. Anyway, although the number of child processes isn't concurrent, would there be a problem with the number of historical child processes in view of ulimits or kern.maxfilesperproc? Anyway suggestions, tips, or links are much appreciated. Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best column type for latitude / longitude?
Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varchar(21) for latitude, and varchar(22) for longitude? ...Rene
Consenus on best column type for Latitude / Longitude?
Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varcar(21) for latitude, and varchar(22) for longitude? ...Rene
Care and feeding of MySQL tables (aka Preventative Maintenance via Cron)
Just curious, what cron jobs do you schedule for automatically checking/repairing/analyzing/optimizing MyISAM tables? I have been doing this periodically, manually for a while, but I figured cron is the way to go. Also, what parameters are you using? I am thinking of running the following cron jobs: mysqlcheck --all-databases --auto-repair (daily) mysqlcheck --all-databases --analyse (weekly) mysqlcheck --all-databases --optimize (weekly) Good idea? Bad? Or just plain ugly? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL says, Ich don't think so (Subquery woes)
SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id ...works. Returns, e.g.: 1234, 3456, 5483, 8382. SELECT history.* FROM history WHERE history.id IN (1234, 3456, 5483, 8382 ) ...works too. But if I try to combine them using a subquery, a la... SELECT history.* FROM history WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id ) ...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I have to kill the connection. I can't figure out why. Any ideas? Any suggestions? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL says, Ich don't think so (Subquery woes)
On 4-Feb-06, at 5:36 PM, Michael Stassen wrote: René Fournier wrote: SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id ...works. Returns, e.g.: 1234, 3456, 5483, 8382. SELECT history.* FROM history WHERE history.id IN (1234, 3456, 5483, 8382 ) ...works too. But if I try to combine them using a subquery, a la... SELECT history.* FROM history WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id ) ...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I have to kill the connection. I can't figure out why. Any ideas? Any suggestions? ...Rene Put EXPLAIN in front of your query to see what mysql does http:// dev.mysql.com/doc/refman/4.1/en/explain.html. You will almost certainly see that the subquery's select_type is DEPENDENT SUBQUERY, which means that the subquery is run for each row of the outer query, rather than just once! Unfortunately, mysql's optimizer is not so good at subqueries yet. Yes, this was what I was thinking later—that it's running the subquery on each row (~60,000 of them), which would explain the CPU load. Oh, and to answer another question, I am on 5.0.16. The efficient way to do this is to store the results of the first query (the subquery) in a temporary table, then join to it to get the right rows. Assuming id is the primary key of table history, the following should do: CREATE TEMPORARY TABLE groupmax (id INT UNSIGNED); LOCK TABLES history READ; INSERT INTO groupmax SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id; SELECT history.* FROM history JOIN groupmax ON history.id = groupmax.id UNLOCK TABLES; DROP TABLE groupmax; Thanks! I will try this Monday. On a related note, does the 'temporary table' approach (creating, locking, dropping) significantly slow down queries? I haven't had to use them yet, and my application is somewhat performance sensitive, so... just curious. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to restrict this query... (need subquery?)
Hello, I have two tables: Accounts and History. Basically, I want to see how much activity each account has during a given period of time. Even if an account has no activity, I still want to see it in the result (naturally with zeros or null). In the history table, there is a column called time_sec—it's a UNIX timestamp. That is the column needed to restrict the counting to a particular day or month. My problem is that either I get all the accounts (good) without restricting to a day or month (bad)... SELECT accounts.id, accounts.account_name, accounts.company_name, history.msg_src, COUNT(history.msg_src) as msg_num FROM accounts LEFT JOIN history ON history.account_id = accounts.id GROUP BY accounts.id, msg_src ORDER BY accounts.id DESC, history.msg_src ASC ... or I get a result that is restricted (good), but without showing all the accounts (bad)... SELECT accounts.id, accounts.account_name, accounts.company_name, history.msg_src, COUNT(history.msg_src) as msg_num FROM accounts LEFT JOIN history ON history.account_id = accounts.id WHERE history.time_sec 1138604400 AND history.time_sec 1138652381 GROUP BY accounts.id, msg_src ORDER BY accounts.id DESC, history.msg_src ASC What I need to do, somehow, is apply that WHERE clause to the COUNT part of the SELECT. Any ideas? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to restrict this query... (need subquery?)
Hi Peter, Yes, after I posted the question, I did some more reading and learned that it's possible to have several conditions in the ON clause. I tried: SELECT accounts.id, accounts.account_name, accounts.company_name, history.msg_src, COUNT(history.msg_src) as msg_num FROM accounts LEFT JOIN history ON history.account_id = accounts.id AND history.time_sec BETWEEN 1138604400 AND 1138652381 GROUP BY accounts.id, history.msg_src ORDER BY accounts.id DESC, history.msg_src ASC ...and it does exactly what I need. Thanks for the reply though. ...Rene On 30-Jan-06, at 4:12 PM, Peter Brawley wrote: René What I need to do, somehow, is apply that WHERE clause to the COUNT part of the SELECT. Any ideas? Did you try moving your WHERE condition to the ON clause? PB - René Fournier wrote: Hello, I have two tables: Accounts and History. Basically, I want to see how much activity each account has during a given period of time. Even if an account has no activity, I still want to see it in the result (naturally with zeros or null). In the history table, there is a column called time_sec—it's a UNIX timestamp. That is the column needed to restrict the counting to a particular day or month. My problem is that either I get all the accounts (good) without restricting to a day or month (bad)... SELECT accounts.id, accounts.account_name, accounts.company_name, history.msg_src, COUNT(history.msg_src) as msg_num FROM accounts LEFT JOIN history ON history.account_id = accounts.id GROUP BY accounts.id, msg_src ORDER BY accounts.id DESC, history.msg_src ASC ... or I get a result that is restricted (good), but without showing all the accounts (bad)... SELECT accounts.id, accounts.account_name, accounts.company_name, history.msg_src, COUNT(history.msg_src) as msg_num FROM accounts LEFT JOIN history ON history.account_id = accounts.id WHERE history.time_sec 1138604400 AND history.time_sec 1138652381 GROUP BY accounts.id, msg_src ORDER BY accounts.id DESC, history.msg_src ASC What I need to do, somehow, is apply that WHERE clause to the COUNT part of the SELECT. Any ideas? ...Rene --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can this SELECT go any faster?
Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can this SELECT go any faster?
Thanks, but I don't think replace will help me, since my time_sec column is not DATE. Here's the table def (well, the part that matters): CREATE TABLE history ( id int(10) unsigned NOT NULL auto_increment, time_sec int(10) unsigned NOT NULL default '0', time_msec smallint(5) unsigned NOT NULL default '0', amount int(11) NOT NULL default '0', account_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY account_id (account_id), KEY time_sec (time_sec), KEY time_msec (time_msec), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ; This is why I am formatting time_sec... so I can refer to them as months, e.g.: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC ...Rene On 4-Jan-06, at 2:05 PM, Gordon Bruce wrote: Try this SELECT replace(left(history.time_sec,7),'-','') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC; This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17. mysql SELECT replace(left(pord_Timestamp,7),'-','') AS month - FROM product_order_main - WHERE perm_user_ID = 'CSRB' - GROUP BY month - ORDER BY pord_Timestamp DESC; ++ | month | ++ | 200511 | | 200510 | | 200509 | | 200508 | | 200507 | | 200506 | | 200505 | | 200504 | | 200503 | | 200502 | | 200501 | | 200412 | | 200411 | | 200410 | | 200409 | | 200408 | | 200407 | | 200406 | | 200405 | | 200404 | | 200403 | | 200402 | | 200401 | | 200312 | | 200311 | ++ 25 rows in set (0.08 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main; +--+ | count(*) | +--+ |80774 | +--+ 1 row in set (0.05 sec) mysql select version(); +---+ | version() | +---+ | 5.0.17-nt | +---+ 1 row in set (0.00 sec) -Original Message- From: René Fournier [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 2:23 PM To: mysql@lists.mysql.com Subject: Can this SELECT go any faster? Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT with WHERE and GROUP BY -- can it go any faster?
I have the following table: CREATE TABLE history ( id int(10) unsigned NOT NULL auto_increment, time_sec int(10) unsigned NOT NULL default '0', account_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY account_id (account_id), KEY time_sec (time_sec), ) TYPE=MyISAM AUTO_INCREMENT=36653 ; I need to know which months have activity for any given account_id. Here is my query (which does give me exactly the data I need, albeit slowly): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.1818 sec) -- The problem is speed. 0.1818 seconds to fetch six rows is too slow. The account in question has about 6000 rows. And there are a total of about 25000 rows in the table. I've at least figured out that the thing that there are two things slowing this down: the WHERE and GROUP BY. E.g., if I drop the WHERE clause (fetch rows for all accounts): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 14 (15 total, Query took 0.0930 sec) -- ...it's about twice as fast (or half as slow :-) And If I drop the GROUP BY, e.g.: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history ORDER BY history.time_sec DESC Showing rows 0 - 29 (23895 total, Query took 0.0008 sec) -- Then it's very fast. Do you guys—and girls!—have any suggestions about how to speed this up, besides adding indices on time_sec and account_id (which I've done)? Thanks for taking a look. ...Rene
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
On 17-Nov-05, at 2:41 PM, [EMAIL PROTECTED] wrote: René Fournier [EMAIL PROTECTED] wrote on 11/17/2005 04:19:25 PM: OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) - --- -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) - --- -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output = = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Well, I have a number of queries that are executed on every page, and I'm just trying to optimize them. I don't presume to be a DBA, but I would like to learn how to tune these queries as much as possible... Thanks for your response. Maybe 0.0267 seconds is as good as it gets. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To upgrade, or not to upgrade...
...that is the question. I have some queries that would possibly benefit from subqueries, which means upgrading my stock Mac OS X Server 10.3.9 installation of MySQL (version 4.1.10a). The question is, and I would appreciate any comments, should I go just to 4.0.26 for the subqueries, or straight to 5.0.15? Also, the machine I am upgrading is a production box, so I am a little nervous about what can (and will) go wrong. Any good advice is much appreciated. Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to do in one SELECT... and now for Performance
Thanks, this is very helpful. I'm finally starting to understand Joins. But now I have a serious performance problem. Using INNER JOIN... SELECT users.*, COUNT(history.user_id) as num_events FROM users INNER JOIN history ON (users.id = history.user_id) GROUP BY users.user_name ORDER BY id DESC This is FAST, but incomplete—doesn't include users without events (0 events in history table). 0.1 seconds to fetch 7 rows. Using LEFT JOIN SELECT users.*, COUNT(history.user_id) as num_events FROM users INNER JOIN history ON (users.id = history.user_id) GROUP BY users.user_name ORDER BY id DESC This is COMPLETE, but slow—includes users even with zero events, but takes 18 seconds to fetch 62 rows. (Incidentally, the history table is big, about 15000 records). So... I would love to see the performance of the Inner Join, but with the all users fetched (even those with zero history events), that is what Left Join gives me. Any more ideas? ...Rene On 29-Jul-05, at 6:01 PM, Ed Reed wrote: select USERS.Name, Count(WINS.user_id) From USERS inner join WINS on WINS.user_id = USERS.id Group By USERS.Name René Fournier [EMAIL PROTECTED] 7/29/05 4:40 PM Let's say I have two tables: USERS id name 1 John 2 Mary 3 Sue 4 Paul 5 David WINS id user_id 1 2 2 4 3 3 4 5 5 1 6 4 7 4 8 2 9 3 10 1 How canin one SELECT statementfetch and display all the users, along with the number of games they each one, e.g.: User Games Won John 2 Mary 2 Sue 2 Paul 3 David 1 Is this a job for Subselects? ...Rene -- 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]
How to do in one SELECT
Let's say I have two tables: USERS id name 1John 2Mary 3Sue 4Paul 5David WINS iduser_id 12 24 33 45 51 64 74 82 93 10 1 How can—in one SELECT statement—fetch and display all the users, along with the number of games they each one, e.g.: UserGames Won John 2 Mary 2 Sue 2 Paul 3 David 1 Is this a job for Subselects? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT problem
I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS id dateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS id name - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT problem and QUESTION OF SPEED
Thanks for the solution. It looks like it would work, but I don't have MySQL 4.1 (which I believe is required for this to work, since this is SUBSELECT, isn't it?). Assuming I have to use two selects, which would you say is faster, creating a temporary table in MySQL, or extracting the data back into PHP, and recreating a SELECT from there? ...Rene On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote: Hi René, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon René Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS id dateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS id name - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY ORDER BY
I'm trying to SELECT the most recent record in a table for each person record in another table. Here's what I have so far: SELECT history.*, persons.person_short_name, persons.person_long_name FROM history, persons WHERE persons.id = history.person_id AND persons.status = 1 GROUP BY history.person_id ORDER BY history.time_sec DESC The good thing: It retrieves DISTINCT persons (no duplicates). The problem: The history rows are not the most recent for each person. What I would need, theoretically, is for the ORDER BY clause to go before GROUP BY, but MYSQL doesn't like that it seems. Any ideas? Thanks. ...René --- René Fournier www.renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE problem
I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()which never happensno rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. ...Ren --- Ren Fournier www.renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY problem with JOINs
I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in historyis that because it is a left-joined table? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT WHERE problem
I am having a problem building a SELECT statement that joins two tables with a WHERE condition. SELECT trucks.* FROM trucks, history WHERE trucks.account_id = '100' AND trucks.status = 'Active' AND history.truck_id = trucks.id This is the tricky bit GROUP BY trucks.id ORDER BY history.time_sec DESC Simply put (or as simply as I can put it :-) , this SELECT should return all trucks in order of their activity (history.time_sec). The problem is when a truck is new to the system and does not have a single record in the history table (and therefore no value for history.time_sec). In that case, the truck is excluded from the SELECTed rowsbut I want it returned, just at the bottom of the list (least active). Any ideas how this can be done? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT WHERE problem
Thanks, the LEFT JOIN worked. I do have a question though, why is it considered best practice to list all non-aggregated columns ( I assume you mean columns from trucks.*) in the GROUP BY statement? I ask because I am interested in fast, secure, standards-compliant code, I'm just not always sure what that is. :-) ...Ren --- Ren Fournier, www.renefournier.com On Aug 9, 2004, at 2:14 PM, [EMAIL PROTECTED] wrote: You need to change your INNER JOIN to a LEFT JOIN SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc One other issue ---IMHO, the SQL engine is being too kind when it allows you to execute a query like SELECT trucks.* GROUP BY . In practically EVERY OTHER SQL-based product you will use, you will be required to list _all_ non-aggregated columns in your GROUP BY statement or you will get an error. Listing every column you want to group on is considered proper SQL format and I highly recommend the practice. If you still want to see everything from your trucks table (like in your original query) you can do this: CREATE TEMPORARY TABLE tmpTruckIDs SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc; SELECT trucks*, tmpTruckIDs.total_seconds FROM trucks INNER JOIN tmpTruckIDs ON tmpTruckIDs.id = trucks.id; DROP TABLE tmpTruckIDs; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ren Fournier [EMAIL PROTECTED] wrote on 08/09/2004 03:56:58 PM: I am having a problem building a SELECT statement that joins two tables with a WHERE condition. SELECT trucks.* FROM trucks, history WHERE trucks.account_id = '100' AND trucks.status = 'Active' AND history.truck_id = trucks.id This is the tricky bit GROUP BY trucks.id ORDER BY history.time_sec DESC Simply put (or as simply as I can put it :-) , this SELECT should return all trucks in order of their activity (history.time_sec). The problem is when a truck is new to the system and does not have a single record in the history table (and therefore no value for history.time_sec). In that case, the truck is excluded from the SELECTed rowsbut I want it returned, just at the bottom of the list (least active). Any ideas how this can be done? ...Rene -- 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]
Problems with a Quote in Select
Hello everyone, I'm trying to Select on a field that contains a doublequote, and can't figure out how to pass it throught the Select statement (couldn't find anything in the docs either...) -=-=-=-=- This doesn't work... $whichpart = AND key1='$selcat'; $result = mysql_query(SELECT * FROM parts WHERE status='online' $whichpart,$db); ...when $selcat contains a double quote. For example, if I echo $selcat, I get: AND key1='36\ Core Shafts' == I can see that mysql is choking on that double quote--do I need to convert it to something else? Thanks. ...Rene --- René Fournier, [EMAIL PROTECTED] Toll-free +1.888.886.2754 Tel +1.403.291.3601 Fax +1.403.250.5228 www.smartslitters.com SmartSlitters International #33, 1339 - 40th Ave NE Calgary AB T2E 8N6 Canada - 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: laptops
I guess I'm a switcher. I've had my iBook for almost a year (running OSX, now 10.2), and just absolutely love it. For the web development I do (MySQL, PHP, Apache), it's just great. ...Rene On Thursday, September 12, 2002, at 02:44 PM, Christian J Hedemark wrote: Apple. I spent the extra $$ for a Titanium PowerBook G4 800MHz but you don't need to spend that much for stellar performance. For about a grand you can get the iBook which runs OS X (a BSD-flavored UNIX), it comes with PHP and Apache already installed, and there is an easily installed binary package for OS X. Apple notebooks are extremely well engineered. Don't compare apple MHz ratings to PC's; they are on a totally different scale. If you have an Apple store nearby, you're in luck. Go there on a saturday morning because they have free classes every saturday morning to show you how to get around in OS X. Anyway I have had my Apple for a couple of months now and I am more than happy with it. On Thursday, September 12, 2002, at 04:21 PM, Edward Peloke wrote: Forgive me for getting off topic but I am thinking of buying a new laptop. Which brands do you all trust? I don't have much to spend at all. I am leaning towards a compaq since they are offering an upgrade to xp pro for free (which I will need) and a free wireless card. I am looking for a decent machine to do php and mysql work on. Thanks, Eddie - 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 --- René Fournier, [EMAIL PROTECTED] Toll-free +1.888.886.2754 Tel +1.403.291.3601 Fax +1.403.250.5228 www.smartslitters.com SmartSlitters International #33, 1339 - 40th Ave NE Calgary AB T2E 8N6 Canada - 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
Storing images in MySQL bad idea, performance-wise?
I'm working on a simple content management system that uses PHP and MySQL for updating a web site's text (stored in a MySQL database). (The PHP scripts that do the updating (my stuf) live on one web server, the actual DB data to be updated (my client's stuff) live on another.) So far, I've only had to he able to update the text content of a site--therefore, I've only had to bother to store textual data in the client's DB. But now the client wants to be able to upload/change/delete certain pictures on their web site--using my CMS tool--so I am faced with the following problem: Do I store all such images in the DB? (Which I understand reduces performance.) Or do I--somehow--store the images as files on the client's web server? And if so, how? (Because my PHP scripts are being executed on a different server.) ...Rene --- René Fournier, [EMAIL PROTECTED] Toll-free +1.888.886.2754 Tel +1.403.291.3601 Fax +1.403.250.5228 www.smartslitters.com SmartSlitters International #33, 1339 - 40th Ave NE Calgary AB T2E 8N6 Canada - 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
Can I be an ASP with PHP?
I have a question to which I'm pretty sure the answer will be no, but I would like to hope I'm wrong... I've developed a very simple Content Management tool--called Europa--that even retarded monkeys can use to change/update text in their web site. It's web-based, user-authenticated (sessions), and runs with PHP4 and MySQL. Now, Europa is pretty much plug and play, so long as the web site is getting its text from a MySQL database. There's a web agency in town that is interested in Europa for their clients. Their clients want to be able to easily and quickly update certain elements of their site without begging some outside webmaster. They would really benefit from Europa. Problem: I don't want to sell Europa, or even install it on someone's web server for a one-time fee. I've spent a long time on this little tool, and want to continue to improve it. So, I would rather license it to companies. They pay a quarterly subscription fee, and get to use Europa as it continues to grow and improve. I'm just a little worried about one thing: If I install Europa on their server, and they pay their paltry quarterly subscription fee, and then decide they don't need any updates, I'm screwed. The value of Europa is much greater than what I want to sell subscriptions to it for (not much--I'm not really greedy), but I need some kind of control. The idea: In order for Joe User to update text on his web site, he comes to my Europa web site, enters his company name, user ID, password, and clicks Login, and--voilà--he sees a handsome list of tables containing the text content of his site--which is pulled from a MySQL database residing on HIS web site's web host. And this is the trick: Can PHP somehow fetch MySQL data over the Internet? Is this possible? If so, is it necessary for me to resort to new, unknown technologies like XML or SOAP, or can I do it with PHP alone? Thanks for your comments. ...Rene --- René Fournier, [EMAIL PROTECTED] Toll-free +1.888.886.2754 Tel +1.403.291.3601 Fax +1.403.250.5228 www.smartslitters.com SmartSlitters International #33, 1339 - 40th Ave NE Calgary AB T2E 8N6 Canada - 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
OSX Migration Problem
After some trial and [much] error, I finally have the latest version of PHP (4.0.6??) and MySQL running under Mac OSX 10.1.2. And they seem to be working... sort of. The only problem is when I try to load some pages that use the PHP Session ID function. Now, these exact pages--the whole site in fact--is already running without incident on my ISP (FatCow), and has been for a while. So I figure there must be a configuration issue to deal with. In any case, here are the errors when the page loads (and that's also interesting, it does load). If any of you can shed light on this, I would very much appreciate it. Thanks. Warning: Cannot send session cache limiter - headers already sent (output started at /Users/rene/Sites/smartslitters/jupiter/europa/header.inc:6) in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 19 Warning: Unable to save MySQL query result in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 44 Warning: Supplied argument is not a valid MySQL result resource in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 45 --- ...Rene -- René Fournier - 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
Mac OSX and a pound of my flesh
Can someone PLEASE explain why I get the following error message under OSX (PHP+MySQL), but not under Windows ME or Linux? Warning: Cannot send session cache limiter - headers already sent (output started at /Users/rene/Sites/smartslitters/jupiter/europa/header.inc:6) in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 19 Warning: Unable to save MySQL query result in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 44 Warning: Supplied argument is not a valid MySQL result resource in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 45 --- ...Rene --- René Fournier [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
Mac OSX !?!?!?
Can someone PLEASE explain why I get the following error message under OSX (PHP+MySQL), but not under Windows ME or Linux? Warning: Cannot send session cache limiter - headers already sent (output started at /Users/rene/Sites/smartslitters/jupiter/europa/header.inc:6) in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 19 Warning: Unable to save MySQL query result in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 44 Warning: Supplied argument is not a valid MySQL result resource in /Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 45 --- After these lines, the pages load fine. I'm using PHP Session ID variables (or trying to)--they work fine as I said under other OSes, but now break under OSX. ...Rene --- René Fournier [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: [PHP] Mac OSX !?!?!?
Somebody suggested I post the source--good idea! So here it is... The
header.inc file, in its entirety:
html
head
titleSite :: Jupiter :: Europa/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
link rel=stylesheet href=../../common/styles.css type=text/css
?php
// DETERMINE THE LANGUAGE FROM THE PARENT DIRECTORY
// GENERATE VARIABLES FOR LANGUAGE, FILENAME AND NAME
$path = explode(/,$PHP_SELF);
if (!$lang) { $lang = $path[sizeof($path)-2]; }
$file = $path[sizeof($path)-1];
$prename = explode(.,$file);
$name = $prename[sizeof($prename)-2];
// SECURITY CHECK
session_start();
if (!$PHPSESSID) { die;
} elseif (!$auth) { die;
} elseif ($auth != $path[sizeof($path)-2]) { die; }
$language = All Languages;
if ($lang == en) { $language = English; }
if ($lang == fr) { $language = FranÁais; }
if ($lang == es) { $language = EspaÒol; }
if ($lang == de) { $language = Deutsch; }
if ($lang == it) { $language = Italiano; }
$priv = 2;
// CONNECT TO DATABASE
$db = mysql_connect(localhost,database,password);
mysql_select_db(database,$db);
// FETCH TABLE NAMES
$listtables = mysql_list_tables(database,$db);
// FETCH SELECTED TABLE DATA
$fields = mysql_list_fields(database, $table, $db);
$columns = mysql_num_fields($fields);
?
script language=JavaScript
!--
function GP_popupConfirmMsg(msg) { //v1.0
document.MM_returnValue = confirm(msg);
}
//--
/script
/head
body bgcolor=#FF text=#00 link=#00 vlink=#00
alink=#00 leftmargin=20 topmargin=20 marginwidth=0
marginheight=0 background=../../common/bg.gif
?php
if ($language) { echo span class=adminnormalgreya
href=../index.php4Europa/a/span; }
if ($table) { echo span class=adminnormalgrey : /spanspan
class=adminnormalgreya href=tables.php4Tables/a/span; }
?
pnbsp;
On Wednesday, January 16, 2002, at 06:01 PM, Richard Baskett wrote:
Kind of hard to say without source code for the session part..
Although the
MySQL warnings, I got those because MySQL does not automatically start
in
OSX, if you know it's started then Im not sure, but if not.. go here to
get
the autostart utility:
http://www.entropy.ch/software/macosx/mysql/
This link will have information and a link of where you can download the
utility.
Rick
The human mind is not capable of grasping the Universe. We are like a
little child entering a huge library. The walls are covered to the
ceilings
with books in many different tongues. The child knows that someone must
have
written these books. It does not know who or how. It does not
understand the
languages in which they are written. But the child notes a definite
plan in
the arrangement of the books---a mysterious order which it does not
comprehend, but only dimly suspects. - Albert Einstein
From: René Fournier [EMAIL PROTECTED]
Date: Wed, 16 Jan 2002 17:50:06 -0700
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: [PHP] Mac OSX !?!?!?
Can someone PLEASE explain why I get the following error message under
OSX (PHP+MySQL), but not under Windows ME or Linux?
Warning: Cannot send session cache limiter - headers already sent
(output started at
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc:6) in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 19
Warning: Unable to save MySQL query result in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 44
Warning: Supplied argument is not a valid MySQL result resource in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 45
---
After these lines, the pages load fine. I'm using PHP Session ID
variables (or trying to)--they work fine as I said under other OSes,
but
now break under OSX.
...Rene
---
René Fournier
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: php-list-
[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 mysql-unsubscribe-
[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: [PHP] Mac OSX !?!?!?
Somebody suggested I post the source--good idea! So here it is... The
header.inc file, in its entirety:
html
head
titleSite :: Jupiter :: Europa/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
link rel=stylesheet href=../../common/styles.css type=text/css
?php
// DETERMINE THE LANGUAGE FROM THE PARENT DIRECTORY
// GENERATE VARIABLES FOR LANGUAGE, FILENAME AND NAME
$path = explode(/,$PHP_SELF);
if (!$lang) { $lang = $path[sizeof($path)-2]; }
$file = $path[sizeof($path)-1];
$prename = explode(.,$file);
$name = $prename[sizeof($prename)-2];
// SECURITY CHECK
session_start();
if (!$PHPSESSID) { die;
} elseif (!$auth) { die;
} elseif ($auth != $path[sizeof($path)-2]) { die; }
$language = All Languages;
if ($lang == en) { $language = English; }
if ($lang == fr) { $language = FranÁais; }
if ($lang == es) { $language = EspaÒol; }
if ($lang == de) { $language = Deutsch; }
if ($lang == it) { $language = Italiano; }
$priv = 2;
// CONNECT TO DATABASE
$db = mysql_connect(localhost,database,password);
mysql_select_db(database,$db);
// FETCH TABLE NAMES
$listtables = mysql_list_tables(database,$db);
// FETCH SELECTED TABLE DATA
$fields = mysql_list_fields(database, $table, $db);
$columns = mysql_num_fields($fields);
?
script language=JavaScript
!--
function GP_popupConfirmMsg(msg) { //v1.0
document.MM_returnValue = confirm(msg);
}
//--
/script
/head
body bgcolor=#FF text=#00 link=#00 vlink=#00
alink=#00 leftmargin=20 topmargin=20 marginwidth=0
marginheight=0 background=../../common/bg.gif
?php
if ($language) { echo span class=adminnormalgreya
href=../index.php4Europa/a/span; }
if ($table) { echo span class=adminnormalgrey : /spanspan
class=adminnormalgreya href=tables.php4Tables/a/span; }
?
pnbsp;
On Wednesday, January 16, 2002, at 06:01 PM, Richard Baskett wrote:
Kind of hard to say without source code for the session part..
Although the
MySQL warnings, I got those because MySQL does not automatically start
in
OSX, if you know it's started then Im not sure, but if not.. go here to
get
the autostart utility:
http://www.entropy.ch/software/macosx/mysql/
This link will have information and a link of where you can download the
utility.
Rick
The human mind is not capable of grasping the Universe. We are like a
little child entering a huge library. The walls are covered to the
ceilings
with books in many different tongues. The child knows that someone must
have
written these books. It does not know who or how. It does not
understand the
languages in which they are written. But the child notes a definite
plan in
the arrangement of the books---a mysterious order which it does not
comprehend, but only dimly suspects. - Albert Einstein
From: René Fournier [EMAIL PROTECTED]
Date: Wed, 16 Jan 2002 17:50:06 -0700
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: [PHP] Mac OSX !?!?!?
Can someone PLEASE explain why I get the following error message under
OSX (PHP+MySQL), but not under Windows ME or Linux?
Warning: Cannot send session cache limiter - headers already sent
(output started at
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc:6) in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 19
Warning: Unable to save MySQL query result in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 44
Warning: Supplied argument is not a valid MySQL result resource in
/Users/rene/Sites/smartslitters/jupiter/europa/header.inc on line 45
---
After these lines, the pages load fine. I'm using PHP Session ID
variables (or trying to)--they work fine as I said under other OSes,
but
now break under OSX.
...Rene
---
René Fournier
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: php-list-
[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 mysql-unsubscribe-
[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
Performance
Is it true that mixing HTML and PHP--switching back and forth with ?php ? tags--slows down performance? (I'm using PHP4.) Is it better to echo output than to drop out of PHP mode? And concerning database connections, my ISP asks that I always close a MySQL connection with a mysql_close()--which I can understand. But I'm curious, if I have to make, say, 10 SELECTs throughout a page (in the header, body, and footer), is it perhaps faster to use the mysql_close() at the very end of the page (in the bottom of the footer.inc)? In other words, is there any disadvantage performance-wise (or stability reason) to open and immediately thereafter close mysql connections? Can anyone recommend an article or two on coding techniques for improving PHP and MySQL performance? I don't wan to spend the rest of my life optimizing my php code, but if I knew a few basic rules about its performance characteristics, I might be able to make better decisions when coding. Thanks. ...Rene --- René Fournier, [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: are my queries bloated?
http://www.tums.com On Friday, December 7, 2001, at 06:58 AM, Erik Price wrote: On Thursday, December 6, 2001, at 08:35 PM, Arjen G. Lentz wrote: There is also the one part that limits your selection (shoeshine.com). So that is very important. I've quoted the table structure of the subprojectweb table above, and the 'subproject_name' field is not indexed. Therefore the server will have to do a table scan on the subprojectweb table, to find the rows that match the specified name. So, add an index on that field, or at least a prefix. You don't need to index all 64 chars of it. I see. If I mentally apply this advice to the rest of my database, it seems that I should index (or apply a prefix index) to any column that holds search criteria -- such as files.file_name or subprojectweb.subproject_name.I would assume that I should really only do this with the most-commonly-searched columns, because indexing every column would be unwieldy. Perhaps I should just go with what I have, and once the database (which will only have 30 users or so for now) is cooking, I can go in and analyze the queries and the optimizer and determine where best to place the indexes. For now, the only indexes I have are the PRIMARY KEYs on the *_id columns (for unique ID numbers) and the UNIQUE INDEX on the middle table (foreign key) between files and projects. I read in my book that one way to do this testing is to just reconstruct a table using CREATE TEMPORARY TABLE and go ahead and make changes to this temporary table, and anything that seems to run faster can be later applied to the actual table. Re-order the list of tables in the FROM, and put the subprojectweb.subproject_name = 'shoeshine.com' bit first after the WHERE, that will also make it clearer for you to read. Is this just for my own personal clarity? I was under the impression that the exact order of the JOINs wouldn't matter very much, but I haven't found any evidence of this yet. Thanks very much for responding to my questions about this, Arjen. Erik - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- René Fournier [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: Mac OSX and MySQL
I have to create a MySQL for my PHP scripts. How do I do this in Mac OSX? There are a bunch of command line tools, but I'm not sure which one[s] to use. Not sure I understand this question. What do you mean by create a MySQL for my PHP? oops, I meant to write create a MySQL user for my PHP scripts. In other words, when my scripts try to connect to the database, they need to supply a user id and password (and that must be certain id/pwd to correspond with the 'real' online server we're using). So I would like to know how to create this user/password under Mac OSX. (Thanks.) Thanks. ...Rene Hope this helps, /Rob ~ Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] Life's unfair - but root password helps! - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- René Fournier [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
Dumped OK, Restore NOT
Little problem with MySQL 3.23.46 under Windows ME. I'm trying to create a database from an .sql file (the dumped remains of a functioning database--also MySQL). The dumping was easy, but I'm having a problem putting it back. I have a dumped file called smarts.sql that starts this way: - # MySQL dump 8.14 # # Host: localhostDatabase: smarts # # Server version3.23.38 # # Table structure for table 'about' # CREATE TABLE about ( id smallint(5) unsigned NOT NULL auto_increment, lang char(2) default NULL, recordname varchar(40) default NULL, key1 tinytext, key2 tinytext, status varchar(10) default NULL, title tinytext, par1 tinytext, PRIMARY KEY (id) ) TYPE=MyISAM; # # Dumping data for table 'about' # INSERT INTO about VALUES (1,'en','about' ---(and so on) This is what I'm doing to try to recreate the database locally: 1. I type mysqladmin create smarts OK--I see a new directory called smarts (no files in it) 2. I type mysql smarts smarts.sql It pauses for a few seconds (seeming to input the file), then returns the DOS prompt with no message, error or otherwise. Seems to have worked, right? But when I check the 'smarts' database directory, there are no files in it. (Incidentally mysqld is running.) What am I doing wrong?? ...Rene --- René Fournier [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: [PHP] Dumped OK, Restore NOT
That's why I cross-posted there as well. But I've found that posting to the mysql list doesn't help, more often than not--perhaps fewer subscribers. Anyways, if any PHP people here are--god forbid--actually using MySQL as their database, I would GREATLY appreciate any help in this matter. I've thoroughly read the mysqldump chapter in the docs, and gone through several tutorials, but have found nothing on actually restoring a database from a dumped file--ON Windows. On Wednesday, December 5, 2001, at 09:49 AM, Kurt Lieber wrote: On Wednesday 05 December 2001 08:02 am, you wrote: Little problem with MySQL 3.23.46 under Windows ME. I This would be an excellent question for the MySQL mailing list. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] --- René Fournier [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: [PHP] Dumped OK, Restore NOT
You know, I tried that, it it STILL didn't work. Then, after remembering to reboot my brain, I took a look at the smarts.sql that my ISP kindly dumped for me, to see what code/statements it might be missing--and lo and behold!--it didn't have a USE database; statement! Adding this one wee line made all the difference in the world. Your suggestions(s) work; I can now restore my database locally. Thanks everyone for your help and patience! (And my apologies for posting off-topic oh PHP--I've been suitably chastised, and will remember to name the subject OT: next time I feel tempted to ignore netiquette.) ...Rene On Wednesday, December 5, 2001, at 10:56 AM, Ernesto wrote: You should try: mysql -u username -p password database dump.sql Example: mysql -u joe -p mypass smarts smarts.sql René Fournier wrote: Little problem with MySQL 3.23.46 under Windows ME. I'm trying to create a database from an .sql file (the dumped remains of a functioning database--also MySQL). The dumping was easy, but I'm having a problem putting it back. I have a dumped file called smarts.sql that starts this way: - # MySQL dump 8.14 # # Host: localhostDatabase: smarts # # Server version3.23.38 # # Table structure for table 'about' # CREATE TABLE about ( id smallint(5) unsigned NOT NULL auto_increment, lang char(2) default NULL, recordname varchar(40) default NULL, key1 tinytext, key2 tinytext, status varchar(10) default NULL, title tinytext, par1 tinytext, PRIMARY KEY (id) ) TYPE=MyISAM; # # Dumping data for table 'about' # INSERT INTO about VALUES (1,'en','about' ---(and so on) This is what I'm doing to try to recreate the database locally: 1. I type mysqladmin create smarts OK--I see a new directory called smarts (no files in it) 2. I type mysql smarts smarts.sql It pauses for a few seconds (seeming to input the file), then returns the DOS prompt with no message, error or otherwise. Seems to have worked, right? But when I check the 'smarts' database directory, there are no files in it. (Incidentally mysqld is running.) What am I doing wrong?? ...Rene --- René Fournier [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
Mac OSX and MySQL
The migration continues... Now I have to get my mysql database running under MySQL 3.23.46 on OSX 10.1.1. I've dumped the structure and contents of the database (on Windows) into a text file, and now I'd like to recreate it in the new enivronment. I think I can use the mysql admin tools via command line (but really, what is the syntax for piping a 'dumped' database into a new database? mysqladmin create database dumpeddb.sql??), but one question about users... I have to create a MySQL for my PHP scripts. How do I do this in Mac OSX? There are a bunch of command line tools, but I'm not sure which one[s] to use. Thanks. ...Rene --- René Fournier [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
MySQL ORDER BY or PHP Sort?
I want to select about 25 rows from a table, and sort them by two criteria. First, by each row's Series field (Baby, Genesis, Super, Predator, Millennium are the various Series, and the order I'd like the rows in the array). Within each Series, I'd like the rows sorted by their Price field, ascending. For example: Baby $5 Baby $10 Baby $15 Genesis $20 Genesis $35 Genesis $50 ...and so on. Now, I know how to structure my MySQL Select statment such that the rows it pulls from the table will be either sorted by Price OR by Series, but not both, in the way I'd like. Does anyone know if it's possibly to do this in the Select statement itself? (I'd rather do it that way, than resort in PHP.) Thanks! ...Rene --- René Fournier [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
MySQL ORDER BY or PHP Sort? Oops.
One more thing, very important: I want to specify the Series sort order, not alphabetically, but by a non-obvious way (BabyGenesisSuperPredatorMillennium)... -- I want to select about 25 rows from a table, and sort them by two criteria. First, by each row's Series field (Baby, Genesis, Super, Predator, Millennium are the various Series, and the order I'd like the rows in the array). Within each Series, I'd like the rows sorted by their Price field, ascending. For example: Baby $5 Baby $10 Baby $15 Genesis $20 Genesis $35 Genesis $50 ...and so on. Now, I know how to structure my MySQL Select statment such that the rows it pulls from the table will be either sorted by Price OR by Series, but not both, in the way I'd like. Does anyone know if it's possibly to do this in the Select statement itself? (I'd rather do it that way, than resort in PHP.) Thanks! ...Rene --- René Fournier [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: MySQL ORDER BY or PHP Sort? Oops.
From: René Fournier [EMAIL PROTECTED]
Date: Mon Dec 03, 2001 06:11:23 PM US/Mountain
To: Benjamin Pflugmann [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
Subject: Re: MySQL ORDER BY or PHP Sort? Oops.
Here is the snippet of code that is presenting a challenge:
---
?php
// FETCH MODELS TECH SPECS
function models ($lang,$db) {
$modelsheader = mysql_fetch_array(mysql_query(SELECT * FROM models
WHERE lang='$lang' AND key1='header',$db));
$result = mysql_query(SELECT * FROM models WHERE key1='data' ORDER
BY price ASC,$db);
mysql_close();
include ('../common/models.inc');
}
models($lang,$db);
// CREATE A MULTI-DIMENSIONAL MODELS ARRAYS BASED ON THE NUMBER OF MODEL
ROWS IN THE TABLE
$i = 0;
do {
$allmodels[$i] = $models;
$i++;
} while ($models = mysql_fetch_array($result));
// NUMBER OF MODELS
$first = 1;
$last = sizeof($allmodels)-1;
$colspan = ($last * 2)+1;
?
---
The problem is, if I sort by the price field, the ordering of the series
gets mixed up (since low-end models in higher-end series cost more than
some higher-end models in lower-end series (confused?? I am :-) If I
just sort by series, I don't get the prices in order within each series
(big problem). And if I sort by BOTH series and price, which is the
logical thing to do, and which many people have kindly suggested (and
which I have tried), well, it doesn't work, because the series must be
sorted in a non-alphabetical way.
I've looked at PHP's myriad sorting functions, and well--maybe I need
more sun (I live in Canada... winter...)--I can't see a 'simple' way to
do a non-numerical, non-alphabetical (user-defined?) sort on the Series
fields, then subsort each series by price.
Many thanks for all the help, fellas.
...Rene
On Monday, December 3, 2001, at 05:31 PM, Benjamin Pflugmann wrote:
Hi.
Because you said, Rene, that you already know how to sort according to
one of both criteria, I presume ordering the series column is not the
problem (e.g. because it is an enum or something alike).
Then the solution would simply be to do something like
SELECT * FROM my_table WHERE ... ORDER BY series, price
I.e. simply list the order criteria which work seperately in the ORDER
BY clause. If this is not appliable, please elaborate.
Btw, it is *much* easier to help if you had quoted a (partial) working
query (which include table and column names and so on). Also, how the
series are stored would have been of interest, because this will
influence how the ordering is specified.
Also, I do not understand why you put emphasis on the fact that the
series is sorted in an unusual way if you later say that you already
know how to order by it?! Is this relevant to the problem at all?
Bye,
Benjamin.
On Tue, Dec 04, 2001 at 12:36:34AM +0200, [EMAIL PROTECTED] wrote:
Well I think mysql doesnt allow you to do this that simple as mysql
doesnt know how you want to sort it exactly. So my suggestion would be to
give a value to each series, eg. Baby = 1,
Genesis=2,Super=3,Predator=4,Millennium=5 and then order by series.
Cheers
Siim Einfeldt
One more thing, very important: I want to specify the Series sort order,
not alphabetically, but by a non-obvious way
(BabyGenesisSuperPredatorMillennium)...
--
I want to select about 25 rows from a table, and sort them by two
criteria. First, by each row's Series field (Baby, Genesis, Super,
Predator, Millennium are the various Series, and the order I'd like
the rows in the array). Within each Series, I'd like the rows sorted by
their Price field, ascending. For example:
Baby $5
Baby $10
Baby $15
Genesis $20
Genesis $35
Genesis $50
...and so on.
Now, I know how to structure my MySQL Select statment such that the rows
it pulls from the table will be either sorted by Price OR by Series, but
not both, in the way I'd like. Does anyone know if it's possibly to do
this in the Select statement itself? (I'd rather do it that way, than
resort in PHP.)
Thanks!
...Rene
[...]
--
[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 mysql-unsubscribe-
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
René Fournier
[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: [PHP] Re: MySQL ORDER BY or PHP Sort? Oops.
You know, in spite of all the good advice I've received, I think that's
what I'll have to do. I say have to because inserting another field
into the table creates some complications (long story... related to the
web admin tool I made for editing the tables). Actually, come to think
of it, there is an existing field that I could reuse for this purpose.
Yes, that will work!
Thanks everyone!
...Rene
On Monday, December 3, 2001, at 06:49 PM, Martin Towell wrote:
can you have another field in your table(s) for a weighting and then
sort
on that?
otherwise you'll have to use php
-Original Message-
From: René Fournier [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 04, 2001 12:16 PM
To: Benjamin Pflugmann; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [PHP] Re: MySQL ORDER BY or PHP Sort? Oops.
From: René Fournier [EMAIL PROTECTED]
Date: Mon Dec 03, 2001 06:11:23 PM US/Mountain
To: Benjamin Pflugmann [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
Subject: Re: MySQL ORDER BY or PHP Sort? Oops.
Here is the snippet of code that is presenting a challenge:
---
?php
// FETCH MODELS TECH SPECS
function models ($lang,$db) {
$modelsheader = mysql_fetch_array(mysql_query(SELECT * FROM models
WHERE lang='$lang' AND key1='header',$db));
$result = mysql_query(SELECT * FROM models WHERE key1='data' ORDER
BY price ASC,$db);
mysql_close();
include ('../common/models.inc');
}
models($lang,$db);
// CREATE A MULTI-DIMENSIONAL MODELS ARRAYS BASED ON THE NUMBER OF MODEL
ROWS IN THE TABLE
$i = 0;
do {
$allmodels[$i] = $models;
$i++;
} while ($models = mysql_fetch_array($result));
// NUMBER OF MODELS
$first = 1;
$last = sizeof($allmodels)-1;
$colspan = ($last * 2)+1;
?
---
The problem is, if I sort by the price field, the ordering of the series
gets mixed up (since low-end models in higher-end series cost more than
some higher-end models in lower-end series (confused?? I am :-) If I
just sort by series, I don't get the prices in order within each series
(big problem). And if I sort by BOTH series and price, which is the
logical thing to do, and which many people have kindly suggested (and
which I have tried), well, it doesn't work, because the series must be
sorted in a non-alphabetical way.
I've looked at PHP's myriad sorting functions, and well--maybe I need
more sun (I live in Canada... winter...)--I can't see a 'simple' way to
do a non-numerical, non-alphabetical (user-defined?) sort on the Series
fields, then subsort each series by price.
Many thanks for all the help, fellas.
...Rene
On Monday, December 3, 2001, at 05:31 PM, Benjamin Pflugmann wrote:
Hi.
Because you said, Rene, that you already know how to sort according to
one of both criteria, I presume ordering the series column is not the
problem (e.g. because it is an enum or something alike).
Then the solution would simply be to do something like
SELECT * FROM my_table WHERE ... ORDER BY series, price
I.e. simply list the order criteria which work seperately in the ORDER
BY clause. If this is not appliable, please elaborate.
Btw, it is *much* easier to help if you had quoted a (partial) working
query (which include table and column names and so on). Also, how the
series are stored would have been of interest, because this will
influence how the ordering is specified.
Also, I do not understand why you put emphasis on the fact that the
series is sorted in an unusual way if you later say that you already
know how to order by it?! Is this relevant to the problem at all?
Bye,
Benjamin.
On Tue, Dec 04, 2001 at 12:36:34AM +0200, [EMAIL PROTECTED] wrote:
Well I think mysql doesnt allow you to do this that simple as mysql
doesnt know how you want to sort it exactly. So my suggestion would be
to
give a value to each series, eg. Baby = 1,
Genesis=2,Super=3,Predator=4,Millennium=5 and then order by series.
Cheers
Siim Einfeldt
One more thing, very important: I want to specify the Series sort order,
not alphabetically, but by a non-obvious way
(BabyGenesisSuperPredatorMillennium)...
--
I want to select about 25 rows from a table, and sort them by two
criteria. First, by each row's Series field (Baby, Genesis, Super,
Predator, Millennium are the various Series, and the order I'd like
the rows in the array). Within each Series, I'd like the rows sorted by
their Price field, ascending. For example:
Baby $5
Baby $10
Baby $15
Genesis $20
Genesis $35
Genesis $50
...and so on.
Now, I know how to structure my MySQL Select statment such that the rows
it pulls from the table will be either sorted by Price OR by Series, but
not both, in the way I'd like. Does anyone know if it's possibly to do
this in the Select statement itself? (I'd rather do it that way, than
resort in PHP.)
Thanks
