Percona's MySQL & InnoDB performance and scalability blog
…mysql> explain (select * from utest where c1=5) union (select * from utest where c2=5) order by ord desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table…
…table which was an integer PK, and in another…table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM…
…1 | 2558807 | | 6739300695 | 1 | 208215 | | 6739297111 | 1 | 2736471 | | 6739296855 | 1 | 2736471 | +————+——–+———-+ 10 rows in set (7.92 sec) MySQL mysql> select forum_id as f from sptest order by…
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table… on col1 would be sorted by col1 i.e. … close, yet slower by ~10s. Another thing that is important…Select_scan 1 1 1 1 1 1 Sort_scan 1 1 1 1 1 1 The first obvious improvement is shown by…
…mysql> explain select * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table… of rows. Another interesting technique is to use sphinx search to accelerate sorting and retrieval which …
…1 | SIMPLE | table | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by…
…advance. What about partitioning in MySQL 5.1? I know … often justified by a large reduction in table size. I sort of stumbled…tables is hard work for the database server. If you do it on the master with INSERT..SELECT…MySQL can’t handle too many of those at once. Aside from star schemas, another…
…they are all straightforward to implement. The bad (sort of) news is that each situation is… permanent solution. Another option is, of course, to change the indexing on the table. For instance, … parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user…
…. Love one another and let…table words2 partition by hash(bucket) partitions 12 as select id % 6 bucket, chars words from…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3,1… necessary. When sorting or grouping …
…MySQL…table: (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id | username | host_id | last_modified | +————+———-+———+———————+ | 1…
Copyright © Percona LLC • Contact Us • MySQL and InnoDB are trademarks of Oracle Corp.