Is there a halfway house between a single database and a full master-slave
setup?
I have a database with one "piggish" table, and I'd like to direct queries that
search the pig to a duplicate database, where it won't affect all the routine
traffic.
I could definitely do this by setting up a
At 12:43 AM 8/30/2011, mysql-plain-digest-h...@lists.mysql.com wrote:
>Not useful to add an index for that. I also wonder why the value is null
>(meaning: unknown, not certain) for almost all records.
It depends on if you want the forest or the trees. A frequently executed
query asks for just t
The "innocuous change" was to add an index for "is_robot" which is true
for 6 out of 20,000 records and null for the rest.
My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly
serviceable query, written years ago and
BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
>
>
>Can you post the EXPLAIN EXTENDED output for your before and after queries?
>also, have you recently run an ANALYZE TABLE on the tables?
// before
mysql> explain extended select
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
-> f
This is a cautionary tale - adding indexes is not always helpful or harmless.
I recently added an index to the "players" table to optimize a common query,
and as a consequence this other query flipped from innocuous to something that
takes infinite time.
select
p1.player_name,g.score1,g.tim
Why would using UNION cause the subqueries to be de-optimized?
explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord
where (gmtdate > date_sub(current_timestamp(),interval 90 day)) and
(player1='13213' or player2='13213' ) group by variation limit 3)
shows using index on
I'll defer to you.
At 01:00 PM 5/28/2007, Baron Schwartz wrote:
>Dave Dyer wrote:
>>Thanks, it turns out you are exactly right. I rewrote
>>the query to keep the "on" immediately following the "left join"
>>and it now works as I wish.
>>I
Thanks, it turns out you are exactly right. I rewrote
the query to keep the "on" immediately following the "left join"
and it now works as I wish.
I'll have to read up on "cross join", but if there
is a mysql bug here, it is that the parser that what I
wrote as "left join" was turned into a cro
I'm trying to construct a join, but the effect I want seems
to be impossible to achieve. In this schema, the "uid"
field is unique in the "players" table, but not in the "ranking"
table (one player per uid, multiple rankings per player)
I want to select player names and rankings for a particila
Is there a trick to remember the sort order? I want
to update some field to be the ordinal of the record according
to some sort criteria.
update record set ordinal=CURRENT_ROW() order by xx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have a family of applications which use the C api to access mysql.
I found by doing a test upgrade to 4.1 that all of these applications
crash, apparently because the structures passed between my applications
and libmysql.dll are incompatible. Recompiling the applications fixes
the problem, b
After installing mysql 4.1 over an existing mysql 4.0.x, the
service refuses to start with this complaint:
Fatal error: Can't open privilege tables: File
'\\pumpkin\f\mysql\share\charsets\?.conf' not found (Errcode: 22)
This is windows, with mysql installed in a nondefault directory.
--
MySQ
While discussing the hazards of having an open mysql port, it occurred
to me that I have never seen any mention of defenses against password
guessing attacks, such as slow response to failed authentication, or
shutting of a particular remote IP that seems to be issuing unsucessful
requests.
Is it
Consider these three queries, the first fails with an error, the second
succeeds and third also succeeds. The only difference the set of records
available in the database to match the query.
In the case that fails, the picture record exists but the batchflow
record does not exist. In fact, NO b
>
>> Getting the same answer, from a simpler query, in infinitely
>> less time, just seems wrong to me.
>
>Makes perfect sense. Simpler queries *are* easier to optimize, you
>know :)
Makes perfect sense. Thanks, I think the relevant points
have been covered.
--
MySQL General Mailing List
Fo
At 05:39 PM 9/3/2004, Donny Simonton wrote:
>It would help if you would say how many entries do you have for changed =0
>and how many are greater than 0.
>
>Since changed is a timestamp you should never get an entry of 0. So the
>query of changed>0 will always do a full table scan. This is defini
Before I post it as a bug, perhaps someone can explain why
this query is not optimized to use the index (it has to examine all 287k rows).
mysql> explain SELECT MAX(changed) FROM archived_stats where changed>0;
++---+---+-+-+--+-+---
our current "situation" is a mysqld crash, it hits an assertion
in my_seek.c.
Assertion failed: pos != (~(my_off_t) 0), file
c:\build\build\mysql-4.0.14\mysys\my_seek.c, line 31
This is with a myisam table containing variable length text fields.
I'm not expecting any solution from this list, b
>
>Please don't take these suggestions as sarcasm. Relying on your tools to catch
>anything other than syntax errors is a bad idea. It builds in a false sense of
>security.
I couldn't possibly disagree that Q/A is a good idea, but so is
defense in depth. That's why we train drivers AND install
I'm a little concerned by the possibilty of a malformed query
accidentally destroying a whole databse. For example, a badly
constructed boolean, intended to select exactly one row, but which
actually modifies all rows.
update mytables set data='who' where row='1234';
(updates 1 row)
verses
One of my mysql databases has developed a tendancy to corrupt a table,
repairable by the repair table query, but it's both alarming and
inconvenient to have the database shut itself down.
this is the report from repair table:
| moonshotj.picture | repair | info | Wrong bytesec: 0-0-0 at 268
Mysql in a shared environment is prone to punish all clients
with "too many connections" errors if one client is hyperactive.
There seems to be no way to deal with this within the standard
framework.
This could be dealt with by adding "max_connections_per_ip" and
"max_connections_per_user" to p
24 matches
Mail list logo
