Index on long Varchar
A table of 250,000 rows had an index on a VARCHAR(27) column always holding a 27-digit generated number; single-value lookups took ~0.3s (explain cost ~12000), which was too slow when run 10,000-20,000 times. Respondents advised avoiding VARCHAR for indexed columns, suggesting CHAR(27) instead, plus OPTOFC=1 to cut network round trips. The winning suggestion was to redefine the column as DECIMAL(27,0) (casting to CHAR if LIKE/MATCHES is needed); the poster reported the cost dropped to 3 and performance improved dramatically.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, we have a table that contains 250.000 Rows (PK id serial, key varchar(27), ...). An index is on the field "key". The varchar ist exactly filled with 27 chars (a generated number). If i filter by this field (key) by one value, the SQL is running 0.3 seconds, and the SQLEXPLAIN has a cost of roughly 12000. The problem: This table is selected nearly 10.000 to 20.000 times, so the runtime of 0.3 seconds is a little slow. How can i "better" index this keyfield? I understand that this long varchar field isnt the best candidate.
Change it to a char. A less than optimal index, but it will help. j. On 12/12/16 9:33 AM, MATTHIAS DJIHANGIROFF wrote: > Hi, > > we have a table that contains 250.000 Rows (PK id serial, key varchar(27), > ....). > > An index is on the field "key". The varchar ist exactly filled with 27 chars > (a generated number). > > If i filter by this field (key) by one value, the SQL is running 0.3 seconds, > and the SQLEXPLAIN has a cost of roughly 12000. > > The problem: This table is selected nearly 10.000 to 20.000 times, so the > runtime of 0.3 seconds is a little slow. > > How can i "better" index this keyfield? I understand that this long varchar > field isnt the best candidate. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
You sure set the environment variable OPTOFC=3D1 for this select. It will reduce the network round trios for this select. Sent from my iPhone > On Dec 12, 2016, at 7:25 AM, Jack Parker <jack.parker4@verizon.net> wrote: > > Change it to a char. A less than optimal index, but it will help. > > j. > > On 12/12/16 9:33 AM, MATTHIAS DJIHANGIROFF wrote: > > Hi, > > > > we have a table that contains 250.000 Rows (PK id serial, key varchar (27), > > ....). > > > > An index is on the field "key". The varchar ist exactly filled with 27 chars > > (a generated number). > > > > If i filter by this field (key) by one value, the SQL is running 0.3 > seconds, > > and the SQLEXPLAIN has a cost of roughly 12000. > > > > The problem: This table is selected nearly 10.000 to 20.000 times, so the > > runtime of 0.3 seconds is a little slow. > > > > How can i "better" index this keyfield? I understand that this long varchar > > field isnt the best candidate. > > > > > > > ***************************************************************************= **** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >
Indexes on VARCHAR columns are notoriously slow. Try it as CHAR(27) and see if that helps. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MATTHIAS DJIHANGIROFF > Sent: Monday, December 12, 2016 08:34 AM > To: ids@iiug.org > Subject: Index on long Varchar [38293] > > Hi, > > we have a table that contains 250.000 Rows (PK id serial, key > varchar(27), ....). > > An index is on the field "key". The varchar ist exactly filled with 27 > chars (a generated number). > > If i filter by this field (key) by one value, the SQL is running 0.3 > seconds, and the SQLEXPLAIN has a cost of roughly 12000. > > The problem: This table is selected nearly 10.000 to 20.000 times, so > the runtime of 0.3 seconds is a little slow. > > How can i "better" index this keyfield? I understand that this long > varchar field isnt the best candidate. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Matthias. If you're sure that field "key" will be allways 27 numeric positions, you'll get a good performance inprove if you define it as DECIMAL(27,0). Anyway, avoid varchar type on indexes (unless it's essential I tun away from varchars). Best regards
This is a very good idea. If you need to do LIKE or MATCHES comparisons, you would have to cast it as a CHAR in the query, though. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > RAFAEL GOMEZ > Sent: Monday, December 12, 2016 10:25 AM > To: ids@iiug.org > Subject: Re: Index on long Varchar [38298] > > Hi Matthias. > > If you're sure that field "key" will be allways 27 numeric positions, > you'll get a good performance inprove if you define it as > DECIMAL(27,0). > > Anyway, avoid varchar type on indexes (unless it's essential I tun away > from varchars). > > Best regards > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
Oh wow, i did not thought about the decimal type. It boosted performance like crazy. Costs are now down to 3 :-) Thank you all so much.
I will try this too. Coincidentally we have another flaw where this might be very helpful :-)