CODEWITHSUNDEEP
phpmysqluuid
GregM
GregM

Reputation: 3734

mysql UUID as String How to speed up my queries

I have my UUIDs as strings, I have thousands of items in my database. My queries that seem even basic seem to takes seconds and seconds to load - when I do more complex ones we're talking minutes of time.

I believe my biggest bottleneck is that I am doing string comparisons thousands of times

Is there a way to leave my database as is - but convert the query syntax to cast the UUIDs as numbers?

Or if that is not going to help how do I go about converting my strings to numbers?

Thank you

Edit:

I have 16 tables all interconnected so difficult to post how all of them are interconnected but here is a few samples:

field  Type
UUID - TEXT 

"SELECT * FROM customer WHERE UUID = '$custUUID'";

if I run it from phpmyadmin this simple query takes .7 seconds and I try to do this hundreds of times = looong loading time

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.6967 sec )
SELECT * 
FROM customer
WHERE UUID = '1234'
LIMIT 0 , 30

This is an example of a more complex query that takes a LONG time reportdate, contract_UUID, customer_UUID, garcom_UUID and city_UUID are all of type text

$query = "SELECT DISTINCT a.weekreportDate FROM contract_sales a 
    INNER JOIN contract b ON a.contract_UUID = b.UUID
    INNER JOIN geoPoint c ON b.customer_UUID = c.customerUUID
    WHERE c.garcom_UUID = '$garbcom'
    AND c.city_UUID = '$cit' ORDER BY `report_date`";

Here is an image of one of the tables in the phpmyadmin http://prntscr.com/1airfd

Upvotes: 0

Views: 2406

Answers (2)

ekta
ekta

Reputation: 1620

There's a faster way that I bumped in while looking at my use case.

Sweet & short - Convert all your string fields(UUID's) to integers(that can be indexed by calculating the unique key using SHA hash function(we will talk of collisions, but that's another time) Here's the simple way to do this (Paramters depend on your individual unique(sub fields, or part of thereof)

select distinct(CONV(SUBSTRING(CAST(SHA(Concat(text_field_1,text_field_2)) AS CHAR), 1, 16), 16, 10))
from table_name

#Post this you will get integer mappings(which can be readily indexed) like -:
"14686325187172356814"
"4176431494776658251"
"17905162221625924418"
"17418684985242853706"
"2795298138913147719"
"17371106411384154394" .. more

the other-way of course (the grind, that is) would have been to create a drop down list of these unique combinations - which is then indexed in these buckets - which is what SHA really does - assigning buckets)

As you noticed I am actually CREATING a unique (primary) key by merging two columns text_field_1 &text_field_2 . You can take substrings or whatever best fits your datatype and range. The explanation for the numbers here, 16, 1, 16,10 and how to design this SHA function is elaborated here - http://greenash.net.au/thoughts/2010/03/generating-unique-integer-ids-from-strings-in-mysql/

Upvotes: 2

user1864610
user1864610

Reputation:

Some suggestions:

Shorten your UUID fields to 16 char from 128

Make sure all your UUID fields are indexed.

Don't SELECT *, It's bad practice and it can increase your load times. Just select what you need.

You say you use 

"SELECT * FROM customer WHERE UUID = '$custUUID'";

thousands of times. Would it be possible to do this:

"SELECT * FROM customer WHERE UUID IN ( '".implode(',',$custUUID)."')";

where $custUUID is an array of UUIDs?

Upvotes: 1

Related Questions