[help] MySQL data into the table of tens of millions of data

The current user account list: Nick, purchase records in table log,
Requirements: real-time query, query by the fuzzy table log.title, table Nick records obtained. The correspondence table nick.uid=log.uid.
Table: because a lot of data Nick has 9 million, log has 3000000 data; now Nick table query is very slow, the log must be greater than Nick, because a user average of at least several purchase records.


If the nick to the table to?
1)Think of the uid segment, but the nick table in uid is not continuous, the middle is broken. And the uid span is very big, 1 to 1000000000, and not continuous.
2)If the nick table, log estimates also score table. Logo and Nick are multi table, so how to query? (program have changed a lot...)
If this table is the key problem of Nick, the uid value is not continuous, the minimum is 10, maximum billion. 100 if the interval to share a table, share 1 Yiyi interval table, not much difference with no scale effect (MySQL once more than 1000000 began to slow).


Table nick1, nick2, nick3, nick4 provides data
mysql> SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick1
    -> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick2
    -> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick3
    -> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick4;
+------------+----------+--------------+
| COUNT(uid) | MIN(uid) | MAX(uid)     |
+------------+----------+--------------+
|   38558758 |       31 | 133152982928 |
|   36101731 |        2 | 133153067302 |
|   12610937 |       87 | 133151412359 |
|     886706 |      533 |   1729210852 |
+------------+----------+--------------+
4 rows in set (1 min 14.58 sec)





These tables are like Nick structure:
mysql> DESC nick;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| uid       | bigint(20)  | NO   | PRI | NULL    |       |
| nick      | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)




There is no other methods or better, for

Started by Cindy at November 22, 2016 - 6:01 PM

Fuzzy search is certainly not

The primary task is to do a full-text index title

Posted by Avivi at November 29, 2016 - 6:09 PM

Alter table log.title (text type) plus the full-text index, and then use the LIKE query to log.title field?
Table Nick to have no advice, ask

Posted by Cindy at November 30, 2016 - 6:53 PM

Now I just need a simple query paging list, without the need for fuzzy query, but requires Pagination.
mysql> SELECT MIN(uid),MAX(uid),COUNT(uid) FROM nick;
+----------+--------------+------------+
| MIN(uid) | MAX(uid)     | COUNT(uid) |
+----------+--------------+------------+
|        2 | 133153067302 |   88090625 |
+----------+--------------+------------+
1 row in set (0.00 sec)



Page LIMIT, page 1000, when up to pages to a big time, query basically crashes, you must restart MYSQL :
mysql> EXPLAIN
    -> SELECT * FROM nick ORDER BY uid ASC LIMIT 90000000,100;
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | nick    | ALL  | NULL          | NULL | NULL    | NULL | 88090625 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)



I am now using method is specified in the ID range scans, but uid is not continuous, and the nick table will not stop to record.
mysql> EXPLAIN
    -> SELECT * FROM nick WHERE uid>2010000 AND uid<2020000 ORDER BY uid ASC;

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref| rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | nick    | range | PRIMARY       | PRIMARY | 8       | NULL|    1 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Posted by Cindy at December 10, 2016 - 7:23 PM