MySQL split table query using varchar column

Question

I have a table in MySQL which I want to query parallel by executing multiple select statements that select non-overlapping equal parts from the table, like:

1. select * from mytable where col between 1 and 1000
2. select * from mytable where col between 1001 and 2000
...

The problem is that the col in my case is varchar. How can I split the query in this case?

In Oracle we can operate with NTILE in combination with rowids. But I didn't find a similar approach in case of MySQL. That's why my thinking is to hash the col value and mod it by the number of equal parts I want to have. Or instead of hashing, dynamically generated rownums could be used.

What would be an optimal solution considering that the table big (xxxM rows) and I want to avoid full table scans for each of the queries?

Look at http://stackoverflow.com/a/3799223/1662973 – Anthony Horne Jul 07 '15 at 06:22 — Anthony Horne, Jul 07 '15 at 06:22

score 0 · Answer 1 · answered Jul 07 '15 at 06:44

0

You can use limit for the purpose of paging, so you will have:

1. select * from mytable limit 0, 1000
2. select * from mytable limit 1000, 1000

answered Jul 07 '15 at 06:44

Lajos Arpad

64,414
37
100
175

If possible, I want to avoid full table scans. – Bruckwald Jul 07 '15 at 08:09
You still want to read from the table, right? So how you want to avoid "full table scans"? What do you refer to as "full table scans"? As far as I know, limit 0, 1000 will not read all the table, it will stop reading after the first 1000 records matching your criteria is found. – Lajos Arpad Jul 08 '15 at 09:21
In case of Oracle you have the rowid which points to the physical address of the given row. I'm looking for a similar approach (or another, optimal one) for MySQL. The problem with limit A, B is, that it yields to A+B rows scan. I.e: A+B records are retrieved and then A recods are dropped. For a webapp using this kind of pagination is fine but for retrieving 1B rows it's not – Bruckwald Jul 08 '15 at 21:09

score 0 · Answer 2 · answered Jul 07 '15 at 06:57

0

you can use casting for varchar column to integer like this cast(col as int)

Regards Tushar

answered Jul 07 '15 at 06:57

Tushar Chaudhary

1
1

How do you cast a character sequence to integer? – Bruckwald Jul 07 '15 at 08:13

kasim · Answer 3 · 2015-07-07T08:57:26.803

0

Without scanning fulltable, it will produce results

SELECT * FROM mytable 
ORDER BY ID
OFFSET 0 ROWS 
FETCH NEXT 100 ROWS ONLY

edited Jul 07 '15 at 08:57

answered Jul 07 '15 at 08:51

kasim

346
2
5
23

MySQL split table query using varchar column

3 Answers3