Does anyone else has a problem where you run the exact same query a few times in a row and every time you get a different set of results? We run optimize index multiple times but it still returns a different set of results. Below is an output from the mysql console, where I run the query three times in a row, once it returned 3 results, once 5 and once 9 results.
Here is the query and the issue seems to be related to group by section, if you remove it you get consistent results every time. Are we doing something wrong with the grouping?
MySQL [(none)]> SELECT altvendorpartcode,upceancode,partdescription,partscatalogcode,brandname,partcategorycode,currentprice,binlocation,qtyonhand,qtyavailable ,organizationid,dealershipid,dealerinventorypartid,isdiscontinued,partid,partscatalogid,partisprivate,isactive,vendorpartcode,vendorcode ,IF(dealerinventorypartid=’’,1,0) AS isredpart FROM dx1partsalldist WHERE MATCH(‘01-0013*’) AND (dealershipid IS NULL OR (dealershipid=‘0c610336-1c49-4887-b1f6-85c5084679ed’ AND organizationid=‘c4b82774-caab-4856-a073-101f39332fdd’)) AND isactive=1 AND partscatalogid IN (50,190,191,192,193,194,370,450,452,470,480,700,800,801,910,1000,1100,1200,1300,1400,1500,1600,1700,1800,1910,2140,2250,8080025,840008086,840008088,840008100,840008202,840008204,840008251,840008301,840008302,840008330,840008355,840008361,840008371,840008372,840008373,840008378,840008634,840008637,840080064,840088789,840089048,840089051,840089057,2000000053,2000000055,2000000056,2000000057,2000000058,2000000060,2000000061,2000000070,2000000085,2000000086,2000000087,2000000088,2000000089,2000000090,2000000091,2000000093,2000000099,2000000102,2000000103,2000000104,2000000105,2000000106,2000000107,2000000108,2000000109,2000000110,2000000111,2000000112,2000000113,2000000114,2000000115,2000000117,2000000120,2000000121,2000000122,2000000123,2000000125,2000000126,2000000127,2000000128,2000000129,2000000130,2000000131,2000000132,2000000133,2000000134,2000000135,2000000136,2000000137,2000000138,2000000139,2000000140,2000000141,2000100404,2000100465,2000101162,2000101319,2000101449,2000101499,2000101527,2000101780,2000102172,2000102179,2000102193,2000102220,2000102585,2000102656,2000102764,2000102791,2000103065,2000103752,2000104234,2000104251,2000104774,2000104907,2000104909,2000104910,2000105022,2000105430,2000105470,2000105477,2000105478,2000105518,2000105683,2000105700,2000105706,2000105743,2000105997,2000106118,2000106119,2000106204,2000106790,2000106965,2000106970,2000107041,2000107218,2000107298,2000107907,2000108073,2000108093,2000108186,2000108189,2000108197,2000108474,2000108499,2000108646,2000108742,2000108775,2000108786,2000108841,2000108845,2000108948,2000109011,2000109037,2000109130,2000109230,2000109593,2020000006,2020000207,2020000440,2020000672,2020000912,2020001243,2020001358,2020001387,2020001420,2020001540,2020001541,2020004033,2020004141,2020004167,2020004206,2020004222,2020005471,2020006138,2020006139,2020006140,2020006142,2020006143,2020006144,2020006145,2020006146,2020006147,2020006148,2020006149,2020006150,2020006151,2020006152,2020006153,2020006154,2020006155,2020006156,2020006157,2020006158,2020006159,2020006160,2020006161,2020006162,2020006163,2020006164) GROUP BY partid WITHIN GROUP ORDER BY isredpart ASC ORDER BY isredpart ASC,altvendorpartcode ASC LIMIT 0,50;
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| altvendorpartcode | upceancode | partdescription | partscatalogcode | brandname | partcategorycode | currentprice | binlocation | qtyonhand | qtyavailable | organizationid | dealershipid | dealerinventorypartid | isdiscontinued | partid | partscatalogid | partisprivate | isactive | vendorpartcode | vendorcode | isredpart |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| 01-0013 | | OIL FILTER KAWASAKI | PU | PARTS UNLIMITED | PAC | 99900 | | 0 | 0 | c4b82774-caab-4856-a073-101f39332fdd | 0c610336-1c49-4887-b1f6-85c5084679ed | ad94fa90-74ac-436a-ba90-c86ed97e3003 | 1 | d400f107-4cfe-45c7-8b16-943296c742d1 | 1000 | 0 | 1 | 010013 | PU | 0 |
| 105-2947 | | REGULATOR YAMAHA 4JH-81960-01-00 | MTA | UMI Starters | PAC | 989900 | | 0 | 0 | | | | 0 | eb9d28dc-b016-4650-b6f0-251fb210efa2 | 840008204 | 0 | 1 | 1052947 | MTA | 1 |
| 105266 | | INT TAIL CBR929RR 00-01 CLR | TR | Clear Alternatives | PAC | 1499500 | | 0 | 0 | | | | 1 | 884e4c5a-d47d-4678-9783-36987aa85ace | 1100 | 0 | 1 | 105266 | TR | 1 |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
3 rows in set (0.01 sec)
MySQL [(none)]> SELECT altvendorpartcode,upceancode,partdescription,partscatalogcode,brandname,partcategorycode,currentprice,binlocation,qtyonhand,qtyavailable ,organizationid,dealershipid,dealerinventorypartid,isdiscontinued,partid,partscatalogid,partisprivate,isactive,vendorpartcode,vendorcode ,IF(dealerinventorypartid=’’,1,0) AS isredpart FROM dx1partsalldist WHERE MATCH(‘01-0013*’) AND (dealershipid IS NULL OR (dealershipid=‘0c610336-1c49-4887-b1f6-85c5084679ed’ AND organizationid=‘c4b82774-caab-4856-a073-101f39332fdd’)) AND isactive=1 AND partscatalogid IN (50,190,191,192,193,194,370,450,452,470,480,700,800,801,910,1000,1100,1200,1300,1400,1500,1600,1700,1800,1910,2140,2250,8080025,840008086,840008088,840008100,840008202,840008204,840008251,840008301,840008302,840008330,840008355,840008361,840008371,840008372,840008373,840008378,840008634,840008637,840080064,840088789,840089048,840089051,840089057,2000000053,2000000055,2000000056,2000000057,2000000058,2000000060,2000000061,2000000070,2000000085,2000000086,2000000087,2000000088,2000000089,2000000090,2000000091,2000000093,2000000099,2000000102,2000000103,2000000104,2000000105,2000000106,2000000107,2000000108,2000000109,2000000110,2000000111,2000000112,2000000113,2000000114,2000000115,2000000117,2000000120,2000000121,2000000122,2000000123,2000000125,2000000126,2000000127,2000000128,2000000129,2000000130,2000000131,2000000132,2000000133,2000000134,2000000135,2000000136,2000000137,2000000138,2000000139,2000000140,2000000141,2000100404,2000100465,2000101162,2000101319,2000101449,2000101499,2000101527,2000101780,2000102172,2000102179,2000102193,2000102220,2000102585,2000102656,2000102764,2000102791,2000103065,2000103752,2000104234,2000104251,2000104774,2000104907,2000104909,2000104910,2000105022,2000105430,2000105470,2000105477,2000105478,2000105518,2000105683,2000105700,2000105706,2000105743,2000105997,2000106118,2000106119,2000106204,2000106790,2000106965,2000106970,2000107041,2000107218,2000107298,2000107907,2000108073,2000108093,2000108186,2000108189,2000108197,2000108474,2000108499,2000108646,2000108742,2000108775,2000108786,2000108841,2000108845,2000108948,2000109011,2000109037,2000109130,2000109230,2000109593,2020000006,2020000207,2020000440,2020000672,2020000912,2020001243,2020001358,2020001387,2020001420,2020001540,2020001541,2020004033,2020004141,2020004167,2020004206,2020004222,2020005471,2020006138,2020006139,2020006140,2020006142,2020006143,2020006144,2020006145,2020006146,2020006147,2020006148,2020006149,2020006150,2020006151,2020006152,2020006153,2020006154,2020006155,2020006156,2020006157,2020006158,2020006159,2020006160,2020006161,2020006162,2020006163,2020006164) GROUP BY partid WITHIN GROUP ORDER BY isredpart ASC ORDER BY isredpart ASC,altvendorpartcode ASC LIMIT 0,50;
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| altvendorpartcode | upceancode | partdescription | partscatalogcode | brandname | partcategorycode | currentprice | binlocation | qtyonhand | qtyavailable | organizationid | dealershipid | dealerinventorypartid | isdiscontinued | partid | partscatalogid | partisprivate | isactive | vendorpartcode | vendorcode | isredpart |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| 01-0013 | | OIL FILTER KAWASAKI | PU | PARTS UNLIMITED | PAC | 99900 | | 0 | 0 | c4b82774-caab-4856-a073-101f39332fdd | 0c610336-1c49-4887-b1f6-85c5084679ed | ad94fa90-74ac-436a-ba90-c86ed97e3003 | 1 | d400f107-4cfe-45c7-8b16-943296c742d1 | 1000 | 0 | 1 | 010013 | PU | 0 |
| 059-010013 | | M/P CABLE FITTING 10-PK. | MTA | Motion Pro | PAC | 189900 | | 0 | 0 | | | | 0 | e8e948d9-33d9-4c4d-ba49-67b2dc89210c | 840008204 | 0 | 1 | 059010013 | MTA | 1 |
| 105-2947 | | REGULATOR YAMAHA 4JH-81960-01-00 | MTA | UMI Starters | PAC | 989900 | | 0 | 0 | | | | 0 | eb9d28dc-b016-4650-b6f0-251fb210efa2 | 840008204 | 0 | 1 | 1052947 | MTA | 1 |
| 105266 | | INT TAIL CBR929RR 00-01 CLR | TR | Clear Alternatives | PAC | 1499500 | | 0 | 0 | | | | 1 | 884e4c5a-d47d-4678-9783-36987aa85ace | 1100 | 0 | 1 | 105266 | TR | 1 |
| 99973-0013-01 | | VIDEO,2-STROKE THEORY | KA | Kawasaki | PHP | 250000 | | 0 | 0 | | | | 0 | 041c7b67-8eb3-4b1a-9293-46c4373fabf7 | 450 | 0 | 1 | 99973001301 | KA | 1 |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
5 rows in set (0.01 sec)
MySQL [(none)]> SELECT altvendorpartcode,upceancode,partdescription,partscatalogcode,brandname,partcategorycode,currentprice,binlocation,qtyonhand,qtyavailable ,organizationid,dealershipid,dealerinventorypartid,isdiscontinued,partid,partscatalogid,partisprivate,isactive,vendorpartcode,vendorcode ,IF(dealerinventorypartid=’’,1,0) AS isredpart FROM dx1partsalldist WHERE MATCH(‘01-0013*’) AND (dealershipid IS NULL OR (dealershipid=‘0c610336-1c49-4887-b1f6-85c5084679ed’ AND organizationid=‘c4b82774-caab-4856-a073-101f39332fdd’)) AND isactive=1 AND partscatalogid IN (50,190,191,192,193,194,370,450,452,470,480,700,800,801,910,1000,1100,1200,1300,1400,1500,1600,1700,1800,1910,2140,2250,8080025,840008086,840008088,840008100,840008202,840008204,840008251,840008301,840008302,840008330,840008355,840008361,840008371,840008372,840008373,840008378,840008634,840008637,840080064,840088789,840089048,840089051,840089057,2000000053,2000000055,2000000056,2000000057,2000000058,2000000060,2000000061,2000000070,2000000085,2000000086,2000000087,2000000088,2000000089,2000000090,2000000091,2000000093,2000000099,2000000102,2000000103,2000000104,2000000105,2000000106,2000000107,2000000108,2000000109,2000000110,2000000111,2000000112,2000000113,2000000114,2000000115,2000000117,2000000120,2000000121,2000000122,2000000123,2000000125,2000000126,2000000127,2000000128,2000000129,2000000130,2000000131,2000000132,2000000133,2000000134,2000000135,2000000136,2000000137,2000000138,2000000139,2000000140,2000000141,2000100404,2000100465,2000101162,2000101319,2000101449,2000101499,2000101527,2000101780,2000102172,2000102179,2000102193,2000102220,2000102585,2000102656,2000102764,2000102791,2000103065,2000103752,2000104234,2000104251,2000104774,2000104907,2000104909,2000104910,2000105022,2000105430,2000105470,2000105477,2000105478,2000105518,2000105683,2000105700,2000105706,2000105743,2000105997,2000106118,2000106119,2000106204,2000106790,2000106965,2000106970,2000107041,2000107218,2000107298,2000107907,2000108073,2000108093,2000108186,2000108189,2000108197,2000108474,2000108499,2000108646,2000108742,2000108775,2000108786,2000108841,2000108845,2000108948,2000109011,2000109037,2000109130,2000109230,2000109593,2020000006,2020000207,2020000440,2020000672,2020000912,2020001243,2020001358,2020001387,2020001420,2020001540,2020001541,2020004033,2020004141,2020004167,2020004206,2020004222,2020005471,2020006138,2020006139,2020006140,2020006142,2020006143,2020006144,2020006145,2020006146,2020006147,2020006148,2020006149,2020006150,2020006151,2020006152,2020006153,2020006154,2020006155,2020006156,2020006157,2020006158,2020006159,2020006160,2020006161,2020006162,2020006163,2020006164) GROUP BY partid WITHIN GROUP ORDER BY isredpart ASC,altvendorpartcode ASC LIMIT 0,50;
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| altvendorpartcode | upceancode | partdescription | partscatalogcode | brandname | partcategorycode | currentprice | binlocation | qtyonhand | qtyavailable | organizationid | dealershipid | dealerinventorypartid | isdiscontinued | partid | partscatalogid | partisprivate | isactive | vendorpartcode | vendorcode | isredpart |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| 01-0013 | | OIL FILTER KAWASAKI | PU | PARTS UNLIMITED | PAC | 99900 | | 0 | 0 | c4b82774-caab-4856-a073-101f39332fdd | 0c610336-1c49-4887-b1f6-85c5084679ed | ad94fa90-74ac-436a-ba90-c86ed97e3003 | 1 | d400f107-4cfe-45c7-8b16-943296c742d1 | 1000 | 0 | 1 | 010013 | PU | 0 |
| 105266 | | INT TAIL CBR929RR 00-01 CLR | TR | Clear Alternatives | PAC | 1499500 | | 0 | 0 | | | | 1 | 884e4c5a-d47d-4678-9783-36987aa85ace | 1100 | 0 | 1 | 105266 | TR | 1 |
| 105-2947 | | REGULATOR YAMAHA 4JH-81960-01-00 | MTA | UMI Starters | PAC | 989900 | | 0 | 0 | | | | 0 | eb9d28dc-b016-4650-b6f0-251fb210efa2 | 840008204 | 0 | 1 | 1052947 | MTA | 1 |
| 059-010013 | | M/P CABLE FITTING 10-PK. | MTA | Motion Pro | PAC | 189900 | | 0 | 0 | | | | 0 | e8e948d9-33d9-4c4d-ba49-67b2dc89210c | 840008204 | 0 | 1 | 059010013 | MTA | 1 |
| 87-0013 | | STEEL HANDLEBAR RACER BLACK | MTA | BVP Umi | PAC | 299900 | | 0 | 0 | | | | 0 | 6db032e9-d719-4703-a8bc-a6b30011d4ff | 840008204 | 0 | 1 | 870013 | MTA | 1 |
±------------------±-----------±---------------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
5 rows in set (0.01 sec)
MySQL [(none)]> SELECT altvendorpartcode,upceancode,partdescription,partscatalogcode,brandname,partcategorycode,currentprice,binlocation,qtyonhand,qtyavailable ,organizationid,dealershipid,dealerinventorypartid,isdiscontinued,partid,partscatalogid,partisprivate,isactive,vendorpartcode,vendorcode ,IF(dealerinventorypartid=’’,1,0) AS isredpart FROM dx1partsalldist WHERE MATCH(‘01-0013*’) AND (dealershipid IS NULL OR (dealershipid=‘0c610336-1c49-4887-b1f6-85c5084679ed’ AND organizationid=‘c4b82774-caab-4856-a073-101f39332fdd’)) AND isactive=1 AND partscatalogid IN (50,190,191,192,193,194,370,450,452,470,480,700,800,801,910,1000,1100,1200,1300,1400,1500,1600,1700,1800,1910,2140,2250,8080025,840008086,840008088,840008100,840008202,840008204,840008251,840008301,840008302,840008330,840008355,840008361,840008371,840008372,840008373,840008378,840008634,840008637,840080064,840088789,840089048,840089051,840089057,2000000053,2000000055,2000000056,2000000057,2000000058,2000000060,2000000061,2000000070,2000000085,2000000086,2000000087,2000000088,2000000089,2000000090,2000000091,2000000093,2000000099,2000000102,2000000103,2000000104,2000000105,2000000106,2000000107,2000000108,2000000109,2000000110,2000000111,2000000112,2000000113,2000000114,2000000115,2000000117,2000000120,2000000121,2000000122,2000000123,2000000125,2000000126,2000000127,2000000128,2000000129,2000000130,2000000131,2000000132,2000000133,2000000134,2000000135,2000000136,2000000137,2000000138,2000000139,2000000140,2000000141,2000100404,2000100465,2000101162,2000101319,2000101449,2000101499,2000101527,2000101780,2000102172,2000102179,2000102193,2000102220,2000102585,2000102656,2000102764,2000102791,2000103065,2000103752,2000104234,2000104251,2000104774,2000104907,2000104909,2000104910,2000105022,2000105430,2000105470,2000105477,2000105478,2000105518,2000105683,2000105700,2000105706,2000105743,2000105997,2000106118,2000106119,2000106204,2000106790,2000106965,2000106970,2000107041,2000107218,2000107298,2000107907,2000108073,2000108093,2000108186,2000108189,2000108197,2000108474,2000108499,2000108646,2000108742,2000108775,2000108786,2000108841,2000108845,2000108948,2000109011,2000109037,2000109130,2000109230,2000109593,2020000006,2020000207,2020000440,2020000672,2020000912,2020001243,2020001358,2020001387,2020001420,2020001540,2020001541,2020004033,2020004141,2020004167,2020004206,2020004222,2020005471,2020006138,2020006139,2020006140,2020006142,2020006143,2020006144,2020006145,2020006146,2020006147,2020006148,2020006149,2020006150,2020006151,2020006152,2020006153,2020006154,2020006155,2020006156,2020006157,2020006158,2020006159,2020006160,2020006161,2020006162,2020006163,2020006164) GROUP BY partid WITHIN GROUP ORDER BY isredpart ASC,altvendorpartcode ASC LIMIT 0,50;
±------------------±-----------±----------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| altvendorpartcode | upceancode | partdescription | partscatalogcode | brandname | partcategorycode | currentprice | binlocation | qtyonhand | qtyavailable | organizationid | dealershipid | dealerinventorypartid | isdiscontinued | partid | partscatalogid | partisprivate | isactive | vendorpartcode | vendorcode | isredpart |
±------------------±-----------±----------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
| 01-0013 | | OIL FILTER KAWASAKI | PU | PARTS UNLIMITED | PAC | 99900 | | 0 | 0 | c4b82774-caab-4856-a073-101f39332fdd | 0c610336-1c49-4887-b1f6-85c5084679ed | ad94fa90-74ac-436a-ba90-c86ed97e3003 | 1 | d400f107-4cfe-45c7-8b16-943296c742d1 | 1000 | 0 | 1 | 010013 | PU | 0 |
| 105266 | | INT TAIL CBR929RR 00-01 CLR | TR | Clear Alternatives | PAC | 1499500 | | 0 | 0 | | | | 1 | 884e4c5a-d47d-4678-9783-36987aa85ace | 1100 | 0 | 1 | 105266 | TR | 1 |
| 99974-0013-01 | | OWN MAN KZ1000P2 | KA | Kawasaki | PHP | 69500 | | 0 | 0 | | | | 1 | 927ff2b5-a7e6-44b9-89a0-750309e2e612 | 450 | 0 | 1 | 99974001301 | KA | 1 |
| 01-0013 | | OIL FILTER KAWASAKI | PU | PARTS UNLIMITED | PAC | 89500 | | 0 | 0 | | | | 1 | d400f107-4cfe-45c7-8b16-943296c742d1 | 1000 | 0 | 1 | 010013 | PU | 1 |
| 87-0013 | | STEEL HANDLEBAR RACER BLACK | MTA | BVP Umi | PAC | 299900 | | 0 | 0 | | | | 0 | 6db032e9-d719-4703-a8bc-a6b30011d4ff | 840008204 | 0 | 1 | 870013 | MTA | 1 |
| 99973-0013-01 | | VIDEO,2-STROKE THEORY | KA | Kawasaki | PHP | 250000 | | 0 | 0 | | | | 0 | 041c7b67-8eb3-4b1a-9293-46c4373fabf7 | 450 | 0 | 1 | 99973001301 | KA | 1 |
| 99963-0013-01 | | S/M SUP KZ1000C2 | KA | Kawasaki | PHP | 109500 | | 0 | 0 | | | | 0 | 0e480a0b-b362-49ed-86f4-66a9a9bce263 | 450 | 0 | 1 | 99963001301 | KA | 1 |
| 99832-0013-01 | | SERVICE MANUAL,EJ800B | KA | Kawasaki | PHP | 919500 | | 0 | 0 | | | | 0 | 4e9906ce-6afc-4297-bc08-a6ada34864d0 | 450 | 0 | 1 | 99832001301 | KA | 1 |
| 99860-0013-01 | | A&P MAN EX650M/N | KA | Kawasaki | PHP | 34900 | | 0 | 0 | | | | 0 | 522b4f09-d36b-4691-b9c6-e2500af176a7 | 450 | 0 | 1 | 99860001301 | KA | 1 |
±------------------±-----------±----------------------------±-----------------±-------------------±-----------------±-------------±------------±----------±-------------±-------------------------------------±-------------------------------------±-------------------------------------±---------------±-------------------------------------±---------------±--------------±---------±---------------±-----------±----------+
9 rows in set (0.01 sec)