Re: does DISTINCT kill ORDER BY?
Markus Hoenicka wrote: Hi, is the following behaviour intended? Are my queries wrong? The output shows only the "Extra" field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? ie: select count(distinct t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id>0; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.1 Partitioning - effect on inode count?
As I only qualify as 'barely literate' as a Unix admin, does anyone know of a good article on inode count, or, even better, one as it pertains to partitioning? I'm thinking about developing a two partitions per month per table schema but I'm worried about the sheer number of files this will create in my data directory. TIA, -- -jp Chuck Norris once survived a suicide bombing. He was the bomber.
does DISTINCT kill ORDER BY?
Hi, is the following behaviour intended? Are my queries wrong? The output shows only the "Extra" field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id; => Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. These results were obtained with: mysql Ver 14.7 Distrib 4.1.21, for portbld-freebsd6.1 (i386) using 5.0 FreeBSD yeti.mininet 6.1-RELEASE FreeBSD 6.1-RELEASE #1: Mon Aug 28 22:24:48 CEST 2006 [EMAIL PROTECTED]:/usr/src/sys/i386/compile/YETI i386 regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
Jocelyn, without more information about how many queries your system has, it's impossible to say. I do not know how MySQL calculates the 'Seconds_Behind_Master' data, but: If your system has bursts of queries, why is the data represented below confusing? What if inbetween your 1st and 2nd 'SHOW SLAVE STATUS' query a large amount of replicated queries were processed causing the slave to temporarily lag behind. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 5 Sep 2006, Jocelyn Fournier wrote: > Hi, > > I encounter a really strange behaviour with some of my slaves servers : > > I'm using MySQL 4.1.20 x86_64 on both master and slave servers. > If I issue three times the command SHOW SLAVE STATUS several times in a > few seconds, I could obtain the following results for the > Seconds_Behind_Master column : > > 0 > 48 > 0 > > I don't understand how it's possible within 1 or 2 seconds to switch > from 0 second behind master to 48 seconds behind master and then back > again to 0 second behind master. > > Any idea of what could be wrong here ? > > Thanks, > Jocelyn > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
Has InnoDb licensing changed to accommodate Oracle?
Has Oracle placed any restrictions on using InnoDb and MySQL now that the original MySQL AB license has expired with Heikki?? What is the name of the new MySQL transaction engine and is anyone using it? Is it any good? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
Hi, Thanks for the answer. I don't think this is what's happening because I can also see within a few seconds : 0 48 0 48 0 for example. Thanks, Jocelyn Michael Loftis a écrit : --On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier <[EMAIL PROTECTED]> wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
--On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier <[EMAIL PROTECTED]> wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large size of MySql binary for Power PC
--On September 5, 2006 1:41:43 PM +0530 Arvind Kumar <[EMAIL PROTECTED]> wrote: Hi! Any help would be appriciated. I found MySQL binaries for embedded system (Processor: Power PC, OS:Linux) There is not much space available in the hard disk. It is around 20 MB. The binary size is large enough, could you please tell me how can i reduce binary size. Stripping, I already did. Disable any engines you won't be using such as bdb and/or innodb and the like at compile time. Switch the optimization flags from -O3 to -Os (size optimized). You can help reduce the size and memory footprints by using a shared library system. 20MB sounds like you're statically linking things. If you use shared libs across the whole system your overall image size will go down. Thanks in Advance Regards, -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave behind master... or not ?
Hi, I encounter a really strange behaviour with some of my slaves servers : I'm using MySQL 4.1.20 x86_64 on both master and slave servers. If I issue three times the command SHOW SLAVE STATUS several times in a few seconds, I could obtain the following results for the Seconds_Behind_Master column : 0 48 0 I don't understand how it's possible within 1 or 2 seconds to switch from 0 second behind master to 48 seconds behind master and then back again to 0 second behind master. Any idea of what could be wrong here ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ndb-storage engine in 5.0.24a-community-max?
Hi, is the ndb-storage-engine for creating clusters included in 5.0.24a-community-max (win)?? if it is, how can I enable it? Or is it still imposible to use clusters on windows-machines? Thx -- Sebastian Mork <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large size of MySql binary for Power PC
Hi! Any help would be appriciated. I found MySQL binaries for embedded system (Processor: Power PC, OS:Linux) There is not much space available in the hard disk. It is around 20 MB. The binary size is large enough, could you please tell me how can i reduce binary size. Stripping, I already did. Thanks in Advance Regards,
