0

I have a relatively large InnoDB table that contains domain names, I want to track when they expire, their traffic rank, owner email, and many parameters like that.

Currently I have around 2 million records, I intend to keep it under 5 million.

I wonder if there is anything else I can do to optimize it, currently a query like this takes around 4-8 seconds:

SELECT * 
FROM domains 
WHERE domain_whois_date > 0 
ORDER BY domain_whois_status asc 
LIMIT 500

I consider that memory and caching related options I set in my.cnf work decently because if I quickly run the same query again it loads in few ms. But table updates very often and most queries are not cached and take longer (so I understand).

So far my best solution is to create a memory based table, a mirror of domains called domains_cache and run searches in that; but seems to crash often, I think it's because this memory table takes almost 100% of ram. Is this the right approach ?

Main InnoDB table takes around 500MB, memory table with same data uses around 2GB. Running on CentOS virtual machine, 2 cores, 4GB ram.

enter image description here

  • Is the SELECT * required, or do you only use some of the columns? – Willem Renzema Dec 3 '16 at 19:48
  • The WHERE domain_whois_date > 0 makes little if any sense at all. @adrianTNT how can a date by 0 or negative? – ypercubeᵀᴹ Dec 3 '16 at 20:54
  • @WillemRenzema I use them all, displaying domains in a table, I think it only makes sense to skip some columns if they would be large text type values, I think* :) – adrianTNT Dec 3 '16 at 23:58
  • @ypercubeᵀᴹ that part indeed is kind of remaining junk from something else; I didn't get what you meant about date by 0. – adrianTNT Dec 3 '16 at 23:59
  • He means all practical values for date will be gt 0. The operator is pointless. You're only going to find efficiency on sorting unless you paginate with logical boundaries. Limit N is not scalable and you will see more benefit with better filtering to reduce scan times. – eroomydna Dec 4 '16 at 2:51
1

In any SQL queries, sequence will be next:

first, select target records:

WHERE domain_whois_date > 0 

then sort result:

ORDER BY domain_whois_status asc 

and only after - take some number of records for return to client:

LIMIT 500

so, in table with 2M records, big part of them will be have Unix date > 0 not many domain was created before - 1/1/1970 and You sort all this records without indexes

You can create or

index for - (domain_whois_date,domain_whois_status)

or at least index for

domain_whois_status

If most of Your domain_whois_date - positive

| improve this answer | |
1

Please create the following index

create index domains_ix_status_date on domains (domain_whois_status,domain_whois_date);

We're traversing through the index where the rows are already ordered by the way we want them (ORDER BY domain_whois_status), we check the condition domain_whois_date > 0 (still within the index structure) and we retrieve only the relevant rows and output them.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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