Timeline for Optimizing 2x 1.8GB tables takes 50 minutes
Current License: CC BY-SA 4.0
26 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 26, 2022 at 5:00 | history | edited | Rick James | CC BY-SA 4.0 |
typos
|
| Dec 26, 2022 at 0:44 | comment | added | adrianTNT | I opened a similar question, maybe you can take a look, but no pressure :) dba.stackexchange.com/questions/321433/… | |
| Dec 23, 2022 at 3:42 | comment | added | Rick James | More MySQL tips to learn here: mysql.rjweb.org | |
| Dec 22, 2022 at 23:14 | vote | accept | adrianTNT | ||
| Dec 22, 2022 at 23:14 | comment | added | adrianTNT | Got it. Thank you. I am learning a lot from these answers / comments. | |
| Dec 22, 2022 at 22:56 | comment | added | Rick James |
@adrianTNT - re "one more" -- Yes, mostly. The innards of FULLTEXT is such that it has a list of the rows that contain contact (etc). It's called an "inverted index". So it does not "go through all the index always". The problem comes in that there is not 'direct' connection between the inverted index and the B+Tree used for that IN.
|
|
| Dec 22, 2022 at 22:53 | comment | added | Rick James |
@adrianTNT - Yes, MATCH works only with FULLTEXT. And LIKE and RLIKE do not work with FULLTEXT, just INDEX, UNIQUE, and PRIMARY KEY`....
|
|
| Dec 22, 2022 at 22:35 | comment | added | adrianTNT |
Sorry, one more question, hopefully the last: the reason my sample query didn't search text just in the two matched url_hash records, is that because full text is stored all in one place and any query goes trough all the index data always ? Did I get that right ?
|
|
| Dec 22, 2022 at 22:32 | comment | added | adrianTNT |
So MATCH AGAINST IN BOOLEAN only works with full text indexes ? (I wasn't sure about this).
|
|
| Dec 22, 2022 at 17:59 | comment | added | Rick James |
@adrianTNT - Even uglier: AND (x1 NOT LIKE '%y% AND x2 NOT LIKE '%y%' ). A normal INDEX is useles for LIKE with leading wildcard; don't add normal index.
|
|
| Dec 22, 2022 at 17:37 | comment | added | adrianTNT |
The proposed query is simple to understand, but I think it would not allow me to add -word negative matches. What if I convert all the 4 columns to normal indexes (not full text), would that perform worse ? And more importantly, can I still do bolean search (+word -word) if is not a full text index ? Thank you.
|
|
| Dec 22, 2022 at 17:28 | comment | added | Rick James | I added the equivalent in SQL. Not pretty. | |
| Dec 22, 2022 at 17:27 | history | edited | Rick James | CC BY-SA 4.0 |
added 1049 characters in body
|
| Dec 22, 2022 at 17:07 | comment | added | adrianTNT |
I can manually split that particular query in PHP code, to just find the few url_hash'es first, and then look for text in the few rows. Unless there is a better solution to tell MySQL the order in which to look ?
|
|
| Dec 22, 2022 at 5:35 | comment | added | Rick James | The problem is that the Optimizer will assume that MATCH is faster than anything else and do that first -- even if it returns thousands of rows. After that it filters down to the two. If that is the typical query, I'll provide some clumsy suggestions. | |
| Dec 22, 2022 at 4:27 | comment | added | adrianTNT |
The url_hash is unique, yes, the LIMIT 2000 is there because it sometimes looks for over 2000 hashes and it should stop if it found 2000. The MATCH AGAINST ... is to also look for a text in URL title, description, etc.
|
|
| Dec 22, 2022 at 4:19 | comment | added | Rick James |
@adrianTNT - And see my new comment on sort_buffer_size
|
|
| Dec 22, 2022 at 4:18 | history | edited | Rick James | CC BY-SA 4.0 |
added 307 characters in body
|
| Dec 22, 2022 at 3:51 | comment | added | Rick James | I suggest picking the sharding chars from a different spot in the MD5. (This gives you a tiny bit of extra protection against an unlikely dup.) | |
| Dec 22, 2022 at 3:49 | comment | added | Rick James |
@adrianTNT - That new SELECT puzzles me. Is url_hash UNIQUE in url_meta_cache? If so the MATCH and LIMIT are of dubious use since there cannot be more than two rows.
|
|
| Dec 22, 2022 at 2:27 | comment | added | adrianTNT | Added a sample query at the end of the question. | |
| Dec 22, 2022 at 2:16 | comment | added | adrianTNT |
And the url_sharding (2) is url_hash truncated to first 1-2 chars, so if I add servers, I can select/move data that has url_sharding=x or url_sharding=xy
|
|
| Dec 22, 2022 at 2:12 | comment | added | adrianTNT |
The 2 indexes that have cardinality = 1 are because I only copied a small portion of a larger table. The 'hash' is a md5() of full url string, and I trunated it to 16 chars, because there are low chanes for conflicts and would not be a huge problem. I use that to shard data across multiple servers.
|
|
| Dec 22, 2022 at 2:09 | comment | added | adrianTNT | Added the variables too ... | |
| Dec 22, 2022 at 2:05 | comment | added | adrianTNT |
I included SHOW CREATE TABLE url_meta_TEST_1; in the question details.
|
|
| Dec 22, 2022 at 1:54 | history | answered | Rick James | CC BY-SA 4.0 |