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
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
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:
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
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
:
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
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.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
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
() 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
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) |
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
,
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º
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` (
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`
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
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:
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
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
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
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
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
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 --
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
)
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é
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
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
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
...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
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
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
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
| 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
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
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
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
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
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
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
-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
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
, 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
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
/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
/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
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
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
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
-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
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
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
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
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
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
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
---
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
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
-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
73 matches
Mail list logo
