0

How fast can a single physical Mysql instance run the following query on a 2 column table that has about 100GB?

SELECT short_url FROM long_to_short WHERE long_url = :in_long_url

I recognize this still leaves out a few important aspects - indexes / DB settings etc. I would truly appreciate a breakdown by the various flavors.

Do you know how this would change if we tiered storage (SSD + mechanicals)?

5
  • 1
    It certainly does. You need to state for a start how the table is indexed. The speed depends on the table definition of your table, speed of your disks, your processor, the amount of memory available in your computer, and the configuration you have defined for MySQL. Question is also off topic.
    – user207421
    Mar 23 at 7:00
  • @Bi Act - One option that you shouldn't ignore is to create a 3 column with the MD5 of the long_url (with index). This column can and should be virtual/generated. This means that it is automatically updated with every INSERT / UPDATE. The advantage of this column is that the index is significantly shorter and therefore faster than with a long string. It also makes sense to set the INDEX unique if possible Mar 23 at 9:17
  • 1
    Please post TEXT results of A) SHOW CREATE TABLE short_url; and B) SHOW TABLE STATUS WHERE name LIKE "short_url"; for analysis. Mar 23 at 10:07
  • Even if you provide the information requested in these comments, I don't think you can get an accurate answer to your question here. The performance depends largely on the MySQL Server tuning, for example buffer pool size, and how much of the table is in RAM, and other details of the power of your server hardware. For all we know, you could be running this on an Arduino. Basically, there are so many factors to performance, that we would just end up asking you to run a benchmark with your data on your server, then then we'd say, "yeah, that's how fast." Mar 23 at 14:45
  • I have a piece of string, how long is it?
    – Vérace
    Apr 7 at 9:31

1 Answer 1

0

First, let's look at datatype and the INDEX:

  • long_url VARCHAR(255), INDEX(long_url, short_url) -- unlikely to be long enough, but 10ms HDD and 1ms SDD.
  • long_url TEXT, INDEX long_url(100) (or some other prefix) -- 20ms and 2ms, but depends heavily on how many long_urls share the same first 100 characters.
  • No index on long_url -- perhaps minutes on either HDD or SSD

Now lets add on a MD5 (or SHAnnn) hash:

  • INDEX(md5, short_url) -- Similar to the first or second, above.
  • INDEX(md5), PRIMARY KEY(short_url) is subtly different.
  • UNIQUE(md5), PRIMARY KEY(short_url) adds some more issues.
  • MariaDB with its automatic hashing (a la your MD5 suggestion) -- Similar performance, simpler(?) syntax. I think the extra column is 'hidden'.
  • If the client and server are on separate servers, it may be better to pick a mechanism that does the hashing in the client. This offloads the CPU effort and shortens the network bandwidth needed.

INDEX versus UNIQUE.

  • UNIQUE is not possible for prefixing (unless you guarantee that the prefix is unique)
  • INDEX may lead to multiple rows returning from a SELECT for some of the cases above.

RAM, and hence the setting of immodb_buffer_pool_size

  • if <16GB -- twice as slow as above.
  • if >200GB -- twice as fast as above after all the data and index is cached.

This year's hardware vs last year's -- I would venture to say that hardware has not improved by 2x in the last decade. The move to SSDs is the only big change.

Rules of Thumb:

  • If you need to reference <100 rows per second -- no problem.
  • If you need to reference >1000 rows per second -- let's dig deeper; please provide more specifics.

Not the answer you're looking for? Browse other questions tagged or ask your own question.