![IP Addresses as Unsigned int
UPDATE users SET ip = INET_ATON
('{$_SERVER['REMOTE_ADDR']}') WHERE user_id =15
192.168.1.100
INET_NTOA() , ip2long() , long2ip()](https://image.slidesharecdn.com/mysql-nasaa-120330025059-phpapp01/85/MySQL-15-320.jpg)
Ad
More Related Content
What's hot (20)
Viewers also liked (20)
Ad
MySQL §¿.§¯§Ñ§ã§Ñ§ß§Ø§Ñ§â§Ô§Ñ§Ý
- 1. MYSQL §±§â§à§Ô§â§Ñ§Þ§Ú§ã§ä §¿.§¯§Ñ§ã§Ñ§ß§Ø§Ñ§â§Ô§Ñ§Ý
- 2. MySQL §ä§å§ç§Ñ§Û ? 1995 §à§ß§í 5 §ã§Ñ§â§Õ §Ñ§ß§ç §Ù§Ñ§ç §Ù§ï§ï§Ý§Õ §Ô§Ñ§â§ã§Ñ§ß ? C / C++ ? §·?§Ô§Ø??§Ý§ï§Ô§é §ß§î MySQL AB ? Facebook, Google, Wikipedia ¡
- 5. MySQL query §Ò§Ú§é§Ú§ç§ï§Õ §Ñ§ß§ç§Ñ§Ñ§â§Ñ§ç §Ù?§Û§Ý§ã ? §´?§Ô§ï§ï§Þ§ï§Ý §Ñ§Ý§Õ§Ñ§Ñ§ß§å§å§Õ ? Limit §Ñ§ê§Ú§Ô§Ý§Ñ§ç ? §°§Ý§à§ß §Õ§Ñ§Ó§ç§Ñ§â Join §Ñ§ê§Ú§Ô§Ý§Ñ§ã§Ñ§ß ?§Ö§Õ §Ò?§ç §Ò§Ñ§Ô§Ñ§ß§í§Ô Select §ç§Ú§Û§ç SELECT * FROM sakila.actor INNER JOIN salila.film_actor USING (actor_id) INNER JOIN salila.film USING (film_id) WHERE sakila.film.title = 'Academy Dinosaur';
- 6. MySQL query §Ò§Ú§é§Ú§ç§ï§Õ §Ñ§ß§ç§Ñ§Ñ§â§Ñ§ç §Ù?§Û§Ý§ã ? §¢?§ç §Ò§Ñ§Ô§Ñ§ß§í§Ô Select §ç§Ú§Û§ç SELECT * ? Count(*) => Count(column_name) ? Unique row SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1 ? Order by RAND() ? ENUM §Ú§Û§Ô §ä§à§Ô§ä§Þ§à§Ý §ä§ï§Þ§Õ§ï§Ô§ä§ä§ï§Û v§Ö§Õ VARCHAR
- 7. Join Decomposition ? SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=post.id WHERE tag.tag='mysql'; ? SELECT * FROM tag WHERE tag='mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);
- 8. §·§ï§â§Ó§ï§ï Join §Ú§Û§Ô §Ú§Ý?? §ç§å§â§Õ§Ñ§ß, §Ñ§ê§Ú§Ô§ä§Ñ§Û §Ñ§Ø§Ý§å§å§Ý§ì§ñ §Ô§ï§Ó§ï§Ý ? Query Cache §Ñ§ê§Ú§Ô§Ý§Ñ§ç ? MyISAM engine ¨C §ä§Ñ§Û §ç?§ã§ß§ï§Ô§ä §Ñ§ê§Ú§Ô§Ý§Ñ§ç ? §°§Ý§à§ß §ã§Ö§â§Ó§Ö§â §Ñ§ê§Ú§Ô§Ý§Ñ§ç ? Join §Ú§Û§Ô In() §ã§à§Ý§Ú§ç ¨C §´§à§Þ §ç§ï§Þ§Ø§ï§ï§ä§ï§Û §ç?§ã§ß§ï§Ô§ä§ï§ß§Õ
- 9. Unbuffered query ? mysql_connect("localhost", "php", "alm65z"); mysql_select_db("phpdb"); $result = mysql_unbuffered_query("SELECT ID, Name FROM conferences;"); $result = mysql_query("SELECT ID, Name FROM conferences;"); while ($row = mysql_fetch_assoc($result)) { extract($row, EXTR_PREFIX_ALL, "conf"); print "$conf_Namen"; }
- 10. Chopping up a Query Mysql> DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 month) rows_affected = 0 do{ rows_affected = do_query( DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 month) LIMIT 10000 ) } while rows_affected > 0
- 11. Query Cache // query cache does NOT work $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // query cache works! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
- 12. Index the Search Fields Mysql> Select count(1) From users Where last_name Like ¡®a%¡¯; 0.25 Mysql> Alter table ¡®users¡¯ Add index(¡®last_name¡¯); Mysql> Select count(1) From users Where last_name Like ¡®a%¡¯; 0.06 Count == 63285
- 13. Index use for Join SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = 15 users.state - §Ú§ß§Õ§Ö§Ü§ã§Ý§ï§Ô§Õ§ã§ï§ß §Ò§Ñ§Ô§Ñ§ß§Ñ
- 14. Procedure Analyse (max_element, max_memory) ? SELECT quantity, sale_order_id FROM `sale_order_queue` PROCEDURE ANALYSE ( 1000000, 256 )
- 15. IP Addresses as Unsigned int UPDATE users SET ip = INET_ATON ('{$_SERVER['REMOTE_ADDR']}') WHERE user_id =15 192.168.1.100 INET_NTOA() , ip2long() , long2ip()
- 16. Partition ? CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6; ? Partition type ? Hash ? Range ? List ? Key
- 17. Range
- 18. Hash
- 19. Key
- 20. List
- 21. Partition §Ñ§ê§Ú§Ô§Ý§Ñ§ç ? CREATE TABLE ts1 ( ? c1 INT, ? c2 VARCHAR(20) ? ) ? PARTITION BY LIST(c1) ( ? PARTITION p0 VALUES IN (0, 3, 6), ? PARTITION p1 VALUES IN (1, 4, 7), ? PARTITION p2 VALUES IN (2, 5, 8) ? ); ? INSERT INTO ts1 VALUES (8, 'mothra'); ? c1 c2 8 mothra
- 22. §¡§ß§ç§Ñ§Ñ§â§Ñ§Ý §ä§Ñ§Ó§î§ã§Ñ§ß§Õ §¢§Ñ§ñ§â§Ý§Ñ§Ý§Ñ§Ñ