sql - Mysql optimization help needed -

sql - Mysql optimization help needed -

i have table table_a 4397898 records

+-------------------+---------------+------+-----+---------+----------------+ | field | type | null | key | default | | +-------------------+---------------+------+-----+---------+----------------+ | id | bigint(11) | no | pri | null | auto_increment | | usrid | int(11) | yes | | null | | | grpid | int(11) | yes | | null | | | catid | int(11) | yes | mul | null | | | folderid | int(11) | no | mul | 5 | | | popid | int(11) | yes | mul | null | | | accid | int(11) | yes | mul | null | | | contentid | bigint(11) | yes | mul | null | | | priority | smallint(6) | yes | | null | | | rating | smallint(6) | no | | 3 | | | fromid | int(11) | yes | | null | | | ctxid | varchar(255) | yes | | null | | | ctxmsgid | varchar(255) | yes | | null | | | starred | enum('y','n') | yes | | n | | | links | enum('y','n') | yes | | n | | +-------------------+---------------+------+-----+---------+----------------+

and indexed below

+---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+ | table | nu | key_name | seq | column_name | col | cardinality | sub_part | packed | null | index_type | +---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+ | table_a | 0 | primary | 1 | id | | 4617132 | null | null | | btree | | table_a | 1 | catindx | 1 | catid | | 256507 | null | null | yes | btree | | table_a | 1 | contentindx | 1 | contentid | | 4617132 | null | null | yes | btree | | table_a | 1 | catindx_2 | 1 | catid | | 18 | null | null | yes | btree | | table_a | 1 | catindx_2 | 2 | popid | | 2013 | null | null | yes | btree | | table_a | 1 | folderidindx | 1 | folderid | | 13619 | null | null | | btree | | table_a | 1 | accindex | 1 | accid | | 1532 | null | null | yes | btree | | table_a | 1 | popindx | 1 | popid | | 1532 | null | null | yes | btree | +---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+

the next query

explain select intcommindx table_a ( (popid in('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) or intpop3indx = -1) , catid = 5 )

explain select id table_a ( (popid in('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) or popid = -1) , catid = 5 ) gives

+----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+ | 1 | simple | table_a | ref | catindx,catindx_2,popindx | catindx | 5 | const | 649800 | using | +----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+

how improve speed of query?

there 850 rows in result. mysql> select -> count(id) -> table_a -> ( -> (popid in('-1',2407,22,1203,1342,1207,3,1254,2663,1250,3461,1251,14,1174,120,2406,2402,325,925,1210,2280,1,1202,1560,775,776,789,777,778,12,779,780,781,782,783,784,785,786,787,788,1209,19,26,9,24,4,25,21,18,1309,967,1212,6,9633,5,2671,17,13,1211,749,752,747,750,748,9302,1470,190,188,9711,9710,9512,11512,9514,9515,9516,11511,9513,9316,9453,1641,4986,1639,1640,7814,10042,9452,11236,11241,11238,11239,11237,11242,11240,1711) or intpop3indx = -1) -> , catid = 5 ); +--------------------+ | count(id) | +--------------------+ | 850 | +--------------------+ 1 row in set (11.22 sec)

what changes can create these 850 records within milliseconds?

this query:

select intcommindx table_a ( (popid in('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) or intpop3indx = -1 ) , catid = 5 )

queries or can quite hard optimize. recommend creating 2 indexes on table , rewriting query. 2 indexes intcommindx(catid, popid, intcommindx) , intcommindx(catid, intpop3indx, intcommindx). new query is:

select intcommindx table_a catid = 5 , popid in ('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) union select intcommindx table_a catid = 5 , intpop3indx = -1;

this allow each subquery satisfied using indexes.

mysql sql optimization indexing query-optimization