Re: show slave status
High Performance MySQL ch 7 provides one of the solutions on that. Look for check_repl. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html On Feb 3, 2008 2:58 AM, Brown, Charles [EMAIL PROTECTED] wrote: Can someone please tell me what is the most effective or preferred method for monitoring replication in MySQL. Recently, my replication stopped as a result of duplicate row found in the slave. Therefore, I would like to monitor and send out an email or alert whenever it is down. Every so often, I would logon to MySQl and issue this command: SHOW SLAVE STATUS. I would like to automate this process. I would like to parse the output of this command and look pertinent info such as seconds_behind_master 10. If it greater than 10, then I'll send out an email saying replication is in error. Charles This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.41 performance on FreeBSD 7.0-RC1 AMD64
Have you checked this? http://www.slideshare.net/sim303/7020-preview/ http://www.freebsd.org/features.html On Jan 22, 2008 4:05 AM, Mikhail Berman [EMAIL PROTECTED] wrote: Hi everyone, Is anyone has experience running MySQL 5.0.41 on FreeBSD 7.0-RC1 AMD64? If you do would you be able to comment on MySQL performance, possible advantages and problems? Regards, -- Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL5.1 estimated release date?
Hi, A silly question. Is there anybody knowing estimated GA release date for MySQL 5.1? I heard it was planed to be released in december this year and the latest release 5.1.22 is RC, but seems that it will not happen soon. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Applying LIMIT to SELECT count(*)
If exact number isn't important, you might want to try table_rows in information_schema.tables or show table status. On Dec 21, 2007 7:53 PM, Urms [EMAIL PROTECTED] wrote: Hi, My task is to limit calculation of total number of items in the database that satisfy certain conditions. I join two tables using WHERE and there are millions of records as the result. When I do SELECT count(*) it takes really too long. The table has appropriate indexes and I experimented with replacing the conditions, etc., so I don't think there is a way to make it work any faster. In my case it would be anough to say that there are more than e.g. 50 000 of items instead of calculating the exact quantity. My question is how to apply a certain limit to count() function in order it would either return the real quantity if it is smaller than the limit or return the limit and stop further calculation, quite same as when using SELECT * FROM ... LIMIT 0, 100 Another option could be estimating approximate quantity in the result but it seems to me much more complex and I honestly don't know where to start from. Thanks! -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14453544.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Applying LIMIT to SELECT count(*)
Oh, I misunderstood,sorry. Using summary tables doesn't work for you? On Dec 22, 2007 3:00 AM, Urms [EMAIL PROTECTED] wrote: The problem is that there are certain conditions after WHERE different for each query and the results number can be very different. -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
You might want to use --single-transaction option when mysqldumping innodb On Dec 1, 2007 1:20 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- 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]
Index not being used when Selecting a certain column
Hello, I encountered weird problem. I upgraded MySQL on my database on my app from 5.0 to the latest 5.1.22. After the upgrade, the app suddenly got very slow. A query, which was executed within about 0.1 sec, now took longer than a few minutes. (Actually, I don't know exactly how long it would take because the time executing the query is so long, I Ctrl-C'd the query) Some explain showed that on 5.1.22, when I include a certain column(VARCHAR(300)) in select list, MySQL ignore the index that should be used. The query has force index to use the right index, but that seems to be ignored. After downgrading to 5.0, MySQL picks the right index. Unfortunately, I cannot connect to the system now, cannot show any explain result, table defijnition, show index etc, but the query that's got slow is a two innodb table joined query with order by. order by column is datetime field and correctly indexed. eacy table contains 2,3 million rows. Is there anybody that has the same experience like this? are there known issues that causes slow query when selecting some columns? Any suggestions would be appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
Have you tried optimize table? On 10/27/07, Josh [EMAIL PROTECTED] wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and AUTO_INCREMENT; is it safe?
Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. Why would you delete data from the slave? The delete statement is for Master, not slave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Thank you for your reply. But I couldn't under stand how --auto-increment-increment and --auto-increment-offset helps me avoid my problem. Could you please explain? On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html See 28.1.5 But there are more reasons to avoid auto-increment in mysql. I haven't run into the problem above, but I have had such problems when restoring backups. Make your data make sense, a mindless counting number just to make a table unique doesn't every make any sense. Session ids, timestamps, combinations of fields all make much better primary keys and it is safer overall to implement a counter function in your app than to trust mysql's -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network address functions in MySQL?
Hello, Good post, but not what i'm looking for. Well, Postgresql seems to be the winner, at least for handling network addresses. (Don't take me wrong. I'm not saying Postgresql is better RDBMS) Thanks. On 5/15/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, js wrote: Hi Baron. Thanks for reply. If I understand correctly, inet_ntoa() and inet_aton() are not capable of handling CIDR notation. Very true, I didn't quite understand the syntax you were using. But you can still use bitwise arithmetic to work around this. Scott Noyes wrote a nice post with examples: http://thenoyes.com/littlenoise/?p=49 On 5/15/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hello, js wrote: Hi. Today I found postgresql's neat feature, inet operators, which allows you to do inet '192.168.1/24' inet '192.168.1.5' http://www.postgresql.org/docs/current/static/functions-net.html Is there anyway to do this using MySQL? Yes. Have a look at the inet_ntoa() and inet_aton() functions. Cheers Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Network address functions in MySQL?
Hi. Today I found postgresql's neat feature, inet operators, which allows you to do inet '192.168.1/24' inet '192.168.1.5' http://www.postgresql.org/docs/current/static/functions-net.html Is there anyway to do this using MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network address functions in MySQL?
Hi Baron. Thanks for reply. If I understand correctly, inet_ntoa() and inet_aton() are not capable of handling CIDR notation. On 5/15/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hello, js wrote: Hi. Today I found postgresql's neat feature, inet operators, which allows you to do inet '192.168.1/24' inet '192.168.1.5' http://www.postgresql.org/docs/current/static/functions-net.html Is there anyway to do this using MySQL? Yes. Have a look at the inet_ntoa() and inet_aton() functions. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A simple 2 table query gets very slow when using ORDER BY
Hi list,
I have 4 simple table.
---
CREATE TABLE employees(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE skills(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE companies(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE employees2skills(
emp_id INT NOT NULL,
skill_id INT NOT NULL,
co_id INT NOT NULL,
mod_time TIMESTAMP NOT NULL,
PRIMARY KEY(emp_id, skill_id),
FOREIGN KEY(emp_id)
REFERENCES employees(id),
FOREIGN KEY(skill_id)
REFERENCES skills(id),
FOREIGN KEY(co_id)
REFERENCES companies(id)
) ENGINE = InnoDB;
ALTER TABLE employees2skills ADD INDEX(mod_time);
---
employees and employees2skills table are
big table containing 100 rows, others are small, just 10 rows or so.
When I join employees and employees2skills, order by query is very fast,
but when I join employees2skills and small companies table,
the query gets very slow.
See below.
---
SELECT * FROM employees e INNER JOIN employees2skills e2s ON e.id = e2s.emp_id
ORDER BY e2s.mod_time limit 5;
+-+++--+---+-+
| id | name | emp_id | skill_id | co_id | mod_time|
+-+++--+---+-+
| 183 | fffmudmykn |183 |1 | 2 | 2007-03-03 17:59:35 |
| 184 | qg{keoohdr |184 |1 | 2 | 2007-03-03 17:59:35 |
| 185 | qoowibsgum |185 |1 | 2 | 2007-03-03 17:59:35 |
| 186 | hxfqnduzt{ |186 |1 | 2 | 2007-03-03 17:59:35 |
| 187 | kyyvuzyqqo |187 |1 | 2 | 2007-03-03 17:59:35 |
+-+++--+---+-+
5 rows in set (0.00 sec)
SELECT * FROM employees2skills e2s INNER JOIN companies c ON c.id =
e2s.co_id ORDER BY e2s.mod_time limit 5;
++--+---+-++---+
| emp_id | skill_id | co_id | mod_time| id | name |
++--+---+-++---+
|183 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
|184 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
|185 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
|186 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
|187 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
++--+---+-++---+
5 rows in set (4.03 sec)
EXPLAIN SELECT * FROM employees2skills e2s INNER JOIN companies c ON
c.id = e2s.co_id ORDER BY e2s.mod_time limit 5;
++-+---+---+---+---+-+---+---+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra|
++-+---+---+---+---+-+---+---+--+
| 1 | SIMPLE | c | index | PRIMARY | name | 62 |
NULL | 7 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | e2s | ref | co_id | co_id | 4 |
test.c.id | 55591 | |
++-+---+---+---+---+-+---+---+--+
2 rows in set (0.00 sec)
---
Probably I'm doing something wrong, but I couldn't figure it out.
Could you please tell me the reason why the above query is slow,
and the solution to get this faster?
Any comments, suggestions and pointers would be greatly appreciated.
Thank you in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A simple 2 table query gets very slow when using ORDER BY
Additional information.
MySQL Version: 5.0.33
Platform: Mac OS X 10.4.8
Machine Spec: 2.16GHZ Intel Core 2 Duo Memory 2GB MHZ SDRAM
Test was done using the following dummy data.
employees table:
20 ascii chars as employees' name
skills:
INSERT INTO skills
(name)
VALUES
('C'),
('C++'),
('C#'),
('Java'),
('Perl'),
('PHP'),
('Python'),
('Ruby'),
('WebDesign'),
('Javascript'),
('Database')
;
companies:
INSERT INTO companies
(name)
VALUES
('Microsoft'),
('Apple'),
('Google'),
('Yahoo'),
('eBay'),
('Amazon'),
('AOL')
;
employees2skills:
INSERT INTO employees2skills SELECT id, 1, 2, NOW() FROM employees;
# Yes, 100 have same skill_id, co_id, and even mod_time.
# selectivity is extreamly low
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database table with unique no duplications
Why do you recommend using trigger instead of unique index? I think when unique index works I don't have any reason to use trigger. If the field you want to be unique is big one, like char(1000) or TEXT, You have to use trigger to save the uniqueness, though. On 3/2/07, Anoop kumar V [EMAIL PROTECTED] wrote: I would suggest you put an after insert trigger which does the validation for you. If 2 fields are already null then it will just return an error and rollback else it will allow the insert to be committed. regards, Anoop On 3/1/07, Kory Wheatley [EMAIL PROTECTED] wrote: I have a database called accountcreation, and I want to setup a table called accountinfo. This table will require that two out of the three fields need to be UNIQUE, I don't want duplications in these fields. One field is a integer value the other field is a character value. What is the best way to set this table up? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A simple 2 table query gets very slow when using ORDER BY
Thank you for answering. I tried your suggestion and it's got fast, but still slow and could not get rid of Using temporary; Using filesort. I don't understand why this simple query is so slow... mysql ALTER TABLE employees2skills ADD INDEX(co_id, mod_time); Query OK, 100 rows affected (24.75 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql analyze table employees2skills; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | test.employees2skills | analyze | status | OK | +---+-+--+--+ 1 row in set (0.01 sec) mysql show index from employees2skills; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | employees2skills | 0 | PRIMARY |1 | emp_id | A | 1000638 | NULL | NULL | | BTREE | | | employees2skills | 0 | PRIMARY |2 | skill_id | A | 1000638 | NULL | NULL | | BTREE | | | employees2skills | 1 | skill_id |1 | skill_id | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | mod_time |1 | mod_time | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | co_id|1 | co_id | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | co_id|2 | mod_time | A | 18 | NULL | NULL | | BTREE | | +--++--+--+-+---+-+--++--++-+ 6 rows in set (0.00 sec) mysql EXPLAIN SELECT * FROM employees2skills e2s INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; ++-+---+---+---+---+-+---+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+---+-+---+---+--+ | 1 | SIMPLE | c | index | PRIMARY | name | 62 | NULL | 7 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | e2s | ref | co_id | co_id | 4 | test.c.id | 55591 | Using index | ++-+---+---+---+---+-+---+---+--+ 2 rows in set (0.00 sec) mysql SELECT * FROM employees2skills e2s INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; ++--+---+-++---+ | emp_id | skill_id | co_id | mod_time| id | name | ++--+---+-++---+ |183 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple | |184 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple | |185 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple | |186 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple | |187 |1 | 2 | 2007-03-03 17:59:35 | 2 | Apple | ++--+---+-++---+ 5 rows in set (1.70 sec) On 3/3/07, Filip Krejci [EMAIL PROTECTED] wrote: Hi, if your selectivity is very low, try to use multiple index on e2s(co_id, mod_time) and force this index in query. Filip employees2skills: INSERT INTO employees2skills SELECT id, 1, 2, NOW() FROM employees; # Yes, 100 have same skill_id, co_id, and even mod_time. # selectivity is extreamly low -- Filip Krejci [EMAIL PROTECTED] Vini, vidi, Linux! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A simple 2 table query gets very slow when using ORDER BY
I agree that my testing data is wrong, but what I was wondering is why the query gets slow only when using join, although without join it gives me lightning answer. BTW, you gave me a big hint! I didn't know about 'cardinality', so searched on the web with the word 'cardinality', which leads me to http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html This page says MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE doesn't produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. So I added FORCE INDEX to my query like this. SELECT * FROM employees2skills e2s FORCE INDEX (mod_time ) INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; ++-+---++---+--+-++-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows| Extra | ++-+---++---+--+-++-+---+ | 1 | SIMPLE | e2s | index | NULL | mod_time | 4 | NULL | 1000638 | | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.e2s.co_id | 1 | | ++-+---++---+--+-++-+---+ 2 rows in set (0.00 sec) it's stunning... So thanks to you, I can solve my problem, thanks you! but I'm not quite sure what's going on. Could you give me some hint please? On 3/3/07, Filip Krejci [EMAIL PROTECTED] wrote: It,s simple. Look at cardinality on last two rows of statement 'show index from e2s'. You have same cardinality on co_id and mod_time both. My solution stands on the fact that mod_time will have much higher selectivity in real data than co_id. (IMO) So, I suppose that you have wrong testing data. Filip Thank you for answering. I tried your suggestion and it's got fast, but still slow and could not get rid of Using temporary; Using filesort. I don't understand why this simple query is so slow... mysql ALTER TABLE employees2skills ADD INDEX(co_id, mod_time); Query OK, 100 rows affected (24.75 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql analyze table employees2skills; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | test.employees2skills | analyze | status | OK | +---+-+--+--+ 1 row in set (0.01 sec) mysql show index from employees2skills; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | employees2skills | 0 | PRIMARY |1 | emp_id | A | 1000638 | NULL | NULL | | BTREE | | | employees2skills | 0 | PRIMARY |2 | skill_id | A | 1000638 | NULL | NULL | | BTREE | | | employees2skills | 1 | skill_id |1 | skill_id | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | mod_time |1 | mod_time | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | co_id|1 | co_id | A | 18 | NULL | NULL | | BTREE | | | employees2skills | 1 | co_id|2 | mod_time | A | 18 | NULL | NULL | | BTREE | | +--++--+--+-+---+-+--++--++-+ 6 rows in set (0.00 sec) mysql EXPLAIN SELECT * FROM employees2skills e2s INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; ++-+---+---+---+---+-+---+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra|
Re: A simple 2 table query gets very slow when using ORDER BY
One thing I don't understand is what MySQL tried to do first. Why did MySQL chose co.name as a key? On 3/3/07, Filip Krejci [EMAIL PROTECTED] wrote: SELECT * FROM employees2skills e2s FORCE INDEX (mod_time ) INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; Yes, this query will be probably the best. This go through mod_time index (which is already sorted) from begin and try to join companies. When row count achieve limit, joining is stopped and result is send. -- Filip Krejci [EMAIL PROTECTED] LINUX-All you need -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EMPTY, like NOT NULL
Hello Nils and Jerry, Thanks you for your quick reply! IMO, using trigger looks cleaner and prettier than Excel-like if(char_length) hack so I will probably give triggers a try. Searching on the net, I found some articles on check constraints in MySQL. http://gilfster.blogspot.com/2005/11/check-constraints-in-mysql-50.html http://db4free.blogspot.com/2006/01/emulating-check-constraints.html http://db4free.blogspot.com/2006/01/emulating-check-constraints-with-views.html regards. On 2/13/07, Nils Meyer [EMAIL PROTECTED] wrote: Hi Js, js wrote: Is there any easy way to implement 'NOT EMPTY' constraint? There currently is no support for CHECK Constraints in MySQL, at least to my knowing. So you'd have to go with a trigger. regards Nils -- 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]
NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this by using subquery or trigger,
but it's a little bit painful.
The simpler the better :)
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EMPTY, like NOT NULL
Thanks for reply and sorry for lack of information.
What I want to know is not how to query,
but how to prevent emtpy data from being inserted in tables.
Let's say I'd like to have a table that doesn't contains any NULL value.
I'd create the table like below.
##
mysql create table t (a char(10) not null);
create table t (a char(10) not null);
Query OK, 0 rows affected (0.07 sec)
mysql insert into t values(NULL);
insert into t values(NULL);
ERROR 1048 (23000): Column 'a' cannot be null
##
well, looks good,
but NOT NULL only prevent NULL, as the name implied.
##
mysql insert into t values('');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
select * from t;
+---+
| a |
+---+
| |
+---+
1 row in set (0.01 sec)
###
Is there any easy way to implement 'NOT EMPTY' constraint?
Thank you in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to pronounce MyISAM and InnoDB
Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Red Hat Advanced Server?
Hi there, Anyone have experience (good or bad) running recent 3.23.49-or-higher MySQL releases on Red Hat Advanced Server? I'm thinking of test driving it and was wondering if anyone has seen noticeable performance improvements over 7.3 or any gotcha's to watch out for. Oddly enough, I'm sort of grasping at straws with this since I seem to have as-of-yet-unresolvable issues with IBM ServeRAID throwing scsi errors in /var/log/messages under high loads. The MySQL status line reports ~720 queries/second (on a quad-Xeon 700Mhz with 4GB memory), and the raid seems to throw errors, although it internally says its fine, for a while, until you get bad stripes and start corrupting the .MYIs. Weird thing is, IBM techs have been out multiple times to replace hardware and to insist that there's nothing wrong with any of it. Seems to be a software issue. Yeah, waay OT for this list, sorry... just ranting here because this has been going on for weeks now and I'm nearly out of ideas. :-) Anyway, if you've got any ideas, no matter how ridiculous they might sound, feel free to pitch them this way. ;) Thanks, =js - 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 in NFS
As always with these things, horses for courses. In my situation, I have a mysql server mounted off a netapp, It is the backend for a website serving 10m+ page impressions per month. I wanted to put the data on a netapp for easy backup ( I can shutdown the server do a snapshot and bring it back up in a few seconds). We started off with 2 servers - a master and slave replication pair, but the early versions of replication proved to be far too unstable. With the latest version, replication seems to be stable and I hope to reimplement the original architecture - (selects off the slaves on local disk, updates on the master off NFS) The database is not huge ( 5-6GB ), everything works pretty well. The website is PHP/Zend Cache based. All the best, John. Matthew Darcy wrote: I have done oracle on NFS and it is not really the best option due to NFS locking. ie a poor network or if the NFS server drops, or the NIS/NIS+ (assuming you are using automount maps) dies this will hold your development/production up no end. Also oracle's table locking (not sure if mysql has this) causes problems over NFS. The only time I have seen it work ok was on a veritas cluster using the Oracle/NFS export as a failover and it worked BAD the machine failed over quite a few times and picked up but the machines had to be very powerfull as there was tons of rollbacks and non commited transactions, and non bound variables over NFS was s slow. I am sure you could do it but it is not wise. I have stored Oracle binarys on NFS so that clients could access oracle and manage it over NFS but never had good performance keeping the data on NFS. Matt. -Original Message- From: Shen, Lei (CIT) [mailto:[EMAIL PROTECTED]] Sent: 12 December 2001 14:28 To: [EMAIL PROTECTED] Subject: Mysql in NFS Hi! Dose anyone has a experience to building mysql database in network files system? and php? can you get me some information? thank you -Original Message- From: Marek Kustka [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 8:23 AM To: [EMAIL PROTECTED] Subject: Embedded MySQL server the outside world Hi folks, does embedded server tcp-listen to the outside world i.e. it could be used by another app or perhaps been accessed by the same app using ODBC? OR is MySQL C API the only way to control it? Thanks, Marek - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
PHP Mysql weirdness
Here's a good one: When doing an update on a table from the CLI, the query is done in under a second (0.01 of a second actually). When I do the _exact_ same query from PHP, the update takes up to 3 minutes. The table is large (1GB, 400k rows) but simple (5 columns, one primary key which is always used - no table scans) Any ideas would be _much_ appreciated. Thanks folks, John. -- John Senior [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
