Databases
StackOverflow2013
Postsdbo
POSpeed of MySQL SELECT (are indexes worth it on columns with little variation)?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpeed of MySQL SELECT (are indexes worth it on columns with little variation)?
    primarykey
    data
    text
    <p>I've done plenty of searching/reading on this topic, but I'm still at a loss for a solution.</p> <p>I have tables with tens or hundreds of thousands of rows, totaling somewhere in the area of 300GB of data. The column I need to select contains a lot of HTML, which may be part of the problem. I am using COMPRESS. The query contains two WHERE clauses:</p> <pre><code> SELECT id, olr_id, COMPRESS(source_html) FROM buildings WHERE scrape_status=1 AND parse_status=0 LIMIT 1; </code></pre> <p>As one might expect, id is a primary key. Additionally, olr_id is a unique index. This query takes something like 160-300sec just to return a result, which is completely unfeasible. Strangely (at least to me), removing the WHERE clause for <code>parse_status</code> results in the query completing in under 2-3 seconds. At first, I thought maybe this was because it was too specific, so I even tried removing <code>scrape_status</code> and only running the <code>parse_status</code> WHERE clause, but apparently it's this specific clause causing the insane increase in execution time.</p> <p>The thing is, I don't know if indexing these columns would even do any good (<code>scrape_status</code> and <code>parse_status</code>), because their range of values is 0-2. I guess that's my main question -- would it help to index a column with such a small variance in values? I read somewhere once that indexing is really best for columns with great variation, but as I said, I'm at a loss for ideas, and 160-300 seconds is just totally unreasonable for hundreds of thousands of records.</p> <p>Any input would be appreciated greatly. If you need additional input to help me, I'd be glad to provide. On that note, these are the EXPLAIN results for each of the three queries:</p> <pre><code>id: 1 select_type: SIMPLE table: building type: ALL possible_keys: null key: null key_len: null ref: null rows: 58664 Extra: Using where </code></pre> <p>I appreciate your time in reading and offering any assistance that you may.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEthan Barron
    1. USEthan Barron
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COJust an idea, I'm not even sure how you would do it but i think it is possible for you to mark the halfway point of your table and then run two queries simultaneously (forking or something like that) with one going through the first half, and one going through second half of your table.
      singulars
      1. POSpeed of MySQL SELECT (are indexes worth it on columns with little variation)?
      1. USthrowaway2013
    2. COIf you added a compound index on parse and scrape status, the query would be well quick. The index as you say would be painful. Why are you getting the value you want like this, as in first one the DBMS bumps into, (Limit 1) ?
      singulars
      1. POSpeed of MySQL SELECT (are indexes worth it on columns with little variation)?
      1. USTony Hopkinson
    3. CO@CppandQtBeginner I'll look into that as a possible solution.
      singulars
      1. POSpeed of MySQL SELECT (are indexes worth it on columns with little variation)?
      1. USEthan Barron
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload