Re: Exceptions with Partition Pruning

[ratheesh balakrishnan]

The primary key is a bigint.


Well if i've tested the scenario wherein the partition is not the first key in the primary key. The observed performance with that use case will be inferior than having regular index on a normal table.


Here is the response time snapshopt from queries with 2k parameters. Both regular and partitioned table gave out results within a second but if we look at the fractions of seconds we will be surely see the difference as given below



# of Parameteres Table Type Total # records Response Time (Seconds)
2000 Parameters Regular Table 3 Million 0.244869
2000 Parameters Partitioned Table with 16 Partitions 3 Million 0.154863
2000 Parameters Partitioned Table with 32 Partitions 3 Million 0.138785
2000 Parameters Partitioned Table with 64 Partitions 3 Million 0.101076

I've created the tables with 16,32,64 partitions and the queries were fetching data from multiple partitions

For example:

mysql> explain partitions select count(*) from search_agent_6m_32 where search_agent_id in (586,1086,1586,2086,2586,3086,3586,4086,4586,5086,5586,6086,6586,7086,7586,8086,8586,9086,9586,10086,10586,11086,11586,12086,12586,13086,13586,14086,14586,15086,15586,16086,16586,17086,17586,18086,18586,19086,19586,20086,20586,21086,21586,22086,22586,23086,23586,24086,24586,25086,25586,26086,26586,27086,27586,28086,28586,29086,29586,30086,30586,31086,31586,32086,32586,33086,33586,34086,34586,35086,35586,36086,36586,37086,37586,38086,38586,39086,39586,40086,40586,41086,41586,42086,42586,43086,43586,44086,44586,45086,45586,46086,46586,47086,47586,48086,48586,49086,49586,50086,50586,51086,51586,52086,52586,53086,53586,54086,54586,55086,55586,56086,56586,57086,57586,58086,58586,59086,59586,60086,60586,61086,61586,62086,62586,63086,63586,64086,64586,65086,65586,66086,66586,67086,67586,68086,68586,69086,69586,70086,70586,71086,71586,72086,72586,73086,73586,74086,74586,75086,75586,76086,76586,77086,77586,78086,78586,79086,79586,80086,80586,81086,81586,82086,82586,83086,83586,84086,84586,85086,85586,86086,86586,87086,87586,88086,88586,89086,89586,90086,90586,91086,91586,92086,92586,93086,93586,94086,94586,95086,95586,96086,96586,97086,97586,98086,98586,99086,99586,100086,100586,101086,101586,102086,102586,103086,103586,104086,104586,105086,105586,106086,106586,107086,107586,108086,108586,109086,109586,110086,110586,111086,111586,112086,112586,113086,113586,114086,114586,115086,115586,116086,116586,117086,117586,118086,118586,119086,119586,120086,120586,121086,121586,122086,122586,123086,123586,124086,124586,125086,125586,126086,126586,127086,127586,128086,128586,129086,129586,130086,130586,131086,131586,132086,132586,133086,133586,134086,134586,135086,135586,136086,136586,137086,137586,138086,138586,139086,139586,140086,140586,141086,141586,142086,142586,143086,143586,144086,144586,145086,145586,146086,146586,147086,147586,148086,148586,149086,149586,150086,150586,151086,151586,152086,152586,153086,153586,154086,154586,155086,155586,156086,156586,157086,157586,158086,158586,159086,159586,160086,160586,161086,161586,162086,162586,163086,163586,164086,164586,165086,165586,166086,166586,167086,167586,168086,168586,169086,169586,170086,170586,171086,171586,172086,172586,173086,173586,174086,174586,175086,175586,176086,176586,177086,177586,178086,178586,179086,179586,180086,180586,181086,181586,182086,182586,183086,183586,184086,184586,185086,185586,186086,186586,187086,187586,188086,188586,189086,189586,190086,190586,191086,191586,192086,192586,193086,193586,194086,194586,195086,195586,196086,196586,197086,197586,198086,198586,199086,199586,200086,200586,201086,201586,202086,202586,203086,203586,204086,204586,205086,205586,206086,206586,207086,207586,208086,208586,209086,209586,210086,210586,211086,211586,212086,212586,213086,213586,214086,214586,215086,215586,216086,216586,217086,217586,218086,218586,219086,219586,220086,220586,221086,221586,222086,222586,223086,223586,224086,224586,225086,225586,226086,226586,227086,227586,228086,228586,229086,229586,230086,230586,231086,231586,232086,232586,233086,233586,234086,234586,235086,235586,236086,236586,237086,237586,238086,238586,239086,239586,240086,240586,241086,241586,242086,242586,243086,243586,244086,244586,245086,245586,246086,246586,247086,247586,248086,248586,249086,249586,250086,250586,251086,251586,252086,252586,253086,253586,254086,254586,255086,255586,256086,256586,257086,257586,258086,258586,259086,259586,260086,260586,261086,261586,262086,262586,263086,263586,264086,264586,265086,265586,266086,266586,267086,267586,268086,268586,269086,269586,270086,270586,271086,271586,272086,272586,273086,273586,274086,274586,275086,275586,276086,276586,277086,277586,278086,278586,279086,279586,280086,280586,281086,281586,282086,282586,283086,283586,284086,284586,285086,285586,286086,286586,287086,287586,288086,288586,289086,289586,290086,290586,291086,291586,292086,292586,293086,293586,294086,294586,295086,295586,296086,296586,297086,297586,298086,298586,299086,299586,300086,300586,301086,301586,302086,302586,303086,303586,304086,304586,305086,305586,306086,306586,307086,307586,308086,308586,309086,309586,310086,310586,311086,311586,312086,312586,313086,313586,314086,314586,315086,315586,316086,316586,317086,317586,318086,318586,319086,319586,320086,320586,321086,321586,322086,322586,323086,323586,324086,324586,325086,325586,326086,326586,327086,327586,328086,328586,329086,329586,330086,330586,331086,331586,332086,332586,333086,333586,334086,334586,335086,335586,336086,336586,337086,337586,338086,338586,339086,339586,340086,340586,341086,341586,342086,342586,343086,343586,344086,344586,345086,345586,346086,346586,347086,347586,348086,348586,349086,349586,350086,350586,351086,351586,352086,352586,353086,353586,354086,354586,355086,355586,356086,356586,357086,357586,358086,358586,359086,359586,360086,360586,361086,361586,362086,362586,363086,363586,364086,364586,365086,365586,366086,366586,367086,367586,368086,368586,369086,369586,370086,370586,371086,371586,372086,372586,373086,373586,374086,374586,375086,375586,376086,376586,377086,377586,378086,378586,379086,379586,380086,380586,381086,381586,382086,382586,383086,383586,384086,384586,385086,385586,386086,386586,387086,387586,388086,388586,389086,389586,390086,390586,391086,391586,392086,392586,393086,393586,394086,394586,395086,395586,396086,396586,397086,397586,398086,398586,399086,399586,400086,400586,401086,401586,402086,402586,403086,403586,404086,404586,405086,405586,406086,406586,407086,407586,408086,408586,409086,409586,410086,410586,411086,411586,412086,412586,413086,413586,414086,414586,415086,415586,416086,416586,417086,417586,418086,418586,419086,419586,420086,420586,421086,421586,422086,422586,423086,423586,424086,424586,425086,425586,426086,426586,427086,427586,428086,428586,429086,429586,430086,430586,431086,431586,432086,432586,433086,433586,434086,434586,435086,435586,436086,436586,437086,437586,438086,438586,439086,439586,440086,440586,441086,441586,442086,442586,443086,443586,444086,444586,445086,445586,446086,446586,447086,447586,448086,448586,449086,449586,450086,450586,451086,451586,452086,452586,453086,453586,454086,454586,455086,455586,456086,456586,457086,457586,458086,458586,459086,459586,460086,460586,461086,461586,462086,462586,463086,463586,464086,464586,465086,465586,466086,466586,467086,467586,468086,468586,469086,469586,470086,470586,471086,471586,472086,472586,473086,473586,474086,474586,475086,475586,476086,476586,477086,477586,478086,478586,479086,479586,480086,480586,481086,481586,482086,482586,483086,483586,484086,484586,485086,485586,486086,486586,487086,487586,488086,488586,489086,489586,490086,490586,491086,491586,492086,492586,493086,493586,494086,494586,495086,495586,496086,496586,497086,497586,498086,498586,499086,499586,500086,500586,501086,501586,502086,502586,503086,503586,504086,504586,505086,505586,506086,506586,507086,507586,508086,508586,509086,509586,510086,510586,511086,511586,512086,512586,513086,513586,514086,514586,515086,515586,516086,516586,517086,517586,518086,518586,519086,519586,520086,520586,521086,521586,522086,522586,523086,523586,524086,524586,525086,525586,526086,526586,527086,527586,528086,528586,529086,529586,530086,530586,531086,531586,532086,532586,533086,533586,534086,534586,535086,535586,536086,536586,537086,537586,538086,538586,539086,539586,540086,540586,541086,541586,542086,542586,543086,543586,544086,544586,545086,545586,546086,546586,547086,547586,548086,548586,549086,549586,550086,550586,551086,551586,552086,552586,553086,553586,554086,554586,555086,555586,556086,556586,557086,557586,558086,558586,559086,559586,560086,560586,561086,561586,562086,562586,563086,563586,564086,564586,565086,565586,566086,566586,567086,567586,568086,568586,569086,569586,570086,570586,571086,571586,572086,572586,573086,573586,574086,574586,575086,575586,576086,576586,577086,577586,578086,578586,579086,579586,580086,580586,581086,581586,582086,582586,583086,583586,584086,584586,585086,585586,586086,586586,587086,587586,588086,588586,589086,589586,590086,590586,591086,591586,592086,592586,593086,593586,594086,594586,595086,595586,596086,596586,597086,597586,598086,598586,599086,599586,600086,600586,601086,601586,602086,602586,603086,603586,604086,604586,605086,605586,606086,606586,607086,607586,608086,608586,609086,609586,610086,610586,611086,611586,612086,612586,613086,613586,614086,614586,615086,615586,616086,616586,617086,617586,618086,618586,619086,619586,620086,620586,621086,621586,622086,622586,623086,623586,624086,624586,625086,625586,626086,626586,627086,627586,628086,628586,629086,629586,630086,630586,631086,631586,632086,632586,633086,633586,634086,634586,635086,635586,636086,636586,637086,637586,638086,638586,639086,639586,640086,640586,641086,641586,642086,642586,643086,643586,644086,644586,645086,645586,646086,646586,647086,647586,648086,648586,649086,649586,650086,650586,651086,651586,652086,652586,653086,653586,654086,654586,655086,655586,656086,656586,657086,657586,658086,658586,659086,659586,660086,660586,661086,661586,662086,662586,663086,663586,664086,664586,665086,665586,666086,666586,667086,667586,668086,668586,669086,669586,670086,670586,671086,671586,672086,672586,673086,673586,674086,674586,675086,675586,676086,676586,677086,677586,678086,678586,679086,679586,680086,680586,681086,681586,682086,682586,683086,683586,684086,684586,685086,685586,686086,686586,687086,687586,688086,688586,689086,689586,690086,690586,691086,691586,692086,692586,693086,693586,694086,694586,695086,695586,696086,696586,697086,697586,698086,698586,699086,699586,700086,700586,701086,701586,702086,702586,703086,703586,704086,704586,705086,705586,706086,706586,707086,707586,708086,708586,709086,709586,710086,710586,711086,711586,712086,712586,713086,713586,714086,714586,715086,715586,716086,716586,717086,717586,718086,718586,719086,719586,720086,720586,721086,721586,722086,722586,723086,723586,724086,724586,725086,725586,726086,726586,727086,727586,728086,728586,729086,729586,730086,730586,731086,731586,732086,732586,733086,733586,734086,734586,735086,735586,736086,736586,737086,737586,738086,738586,739086,739586,740086,740586,741086,741586,742086,742586,743086,743586,744086,744586,745086,745586,746086,746586,747086,747586,748086,748586,749086,749586,750086,750586,751086,751586,752086,752586,753086,753586,754086,754586,755086,755586,756086,756586,757086,757586,758086,758586,759086,759586,760086,760586,761086,761586,762086,762586,763086,763586,764086,764586,765086,765586,766086,766586,767086,767586,768086,768586,769086,769586,770086,770586,771086,771586,772086,772586,773086,773586,774086,774586,775086,775586,776086,776586,777086,777586,778086,778586,779086,779586,780086,780586,781086,781586,782086,782586,783086,783586,784086,784586,785086,785586,786086,786586,787086,787586,788086,788586,789086,789586,790086,790586,791086,791586,792086,792586,793086,793586,794086,794586,795086,795586,796086,796586,797086,797586,798086,798586,799086,799586,800086,800586,801086,801586,802086,802586,803086,803586,804086,804586,805086,805586,806086,806586,807086,807586,808086,808586,809086,809586,810086,810586,811086,811586,812086,812586,813086,813586,814086,814586,815086,815586,816086,816586,817086,817586,818086,818586,819086,819586,820086,820586,821086,821586,822086,822586,823086,823586,824086,824586,825086,825586,826086,826586,827086,827586,828086,828586,829086,829586,830086,830586,831086,831586,832086,832586,833086,833586,834086,834586,835086,835586,836086,836586,837086,837586,838086,838586,839086,839586,840086,840586,841086,841586,842086,842586,843086,843586,844086,844586,845086,845586,846086,846586,847086,847586,848086,848586,849086,849586,850086,850586,851086,851586,852086,852586,853086,853586,854086,854586,855086,855586,856086,856586,857086,857586,858086,858586,859086,859586,860086,860586,861086,861586,862086,862586,863086,863586,864086,864586,865086,865586,866086,866586,867086,867586,868086,868586,869086,869586,870086,870586,871086,871586,872086,872586,873086,873586,874086,874586,875086,875586,876086,876586,877086,877586,878086,878586,879086,879586,880086,880586,881086,881586,882086,882586,883086,883586,884086,884586,885086,885586,886086,886586,887086,887586,888086,888586,889086,889586,890086,890586,891086,891586,892086,892586,893086,893586,894086,894586,895086,895586,896086,896586,897086,897586,898086,898586,899086,899586,900086,900586,901086,901586,902086,902586,903086,903586,904086,904586,905086,905586,906086,906586,907086,907586,908086,908586,909086,909586,910086,910586,911086,911586,912086,912586,913086,913586,914086,914586,915086,915586,916086,916586,917086,917586,918086,918586,919086,919586,920086,920586,921086,921586,922086,922586,923086,923586,924086,924586,925086,925586,926086,926586,927086,927586,928086,928586,929086,929586,930086,930586,931086,931586,932086,932586,933086,933586,934086,934586,935086,935586,936086,936586,937086,937586,938086,938586,939086,939586,940086,940586,941086,941586,942086,942586,943086,943586,944086,944586,945086,945586,946086,946586,947086,947586,948086,948586,949086,949586,950086,950586,951086,951586,952086,952586,953086,953586,954086,954586,955086,955586,956086,956586,957086,957586,958086,958586,959086,959586,960086,960586,961086,961586,962086,962586,963086,963586,964086,964586,965086,965586,966086,966586,967086,967586,968086,968586,969086,969586,970086,970586,971086,971586,972086,972586,973086,973586,974086,974586,975086,975586,976086,976586,977086,977586,978086,978586,979086,979586,980086,980586,981086,981586,982086,982586,983086,983586,984086,984586,985086,985586,986086,986586,987086,987586,988086,988586,989086,989586,990086,990586,991086,991586,992086,992586,993086,993586,994086,994586,995086,995586,996086,996586,997086,997586,998086,998586,999086,999586);
+----+-------------+--------------------+-------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | search_agent_6m_32 | p2,p6,p10,p14,p18,p22,p26,p30 | range | PRIMARY | PRIMARY | 8 | NULL | 1999 | Using where; Using index |
+----+-------------+--------------------+-------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)