Jump to content

Advice For Users With Massive Databases

Recommended Posts

As a developer I get very excited when I see something working at it's maximum performance. If you are new to SQL databases here is a little trick for increasing performance if you ever have a large amount of entries. The trick is called Indexing. I've personally known about this but never had the opportunity to test it on a large scale.Firstly, I have a table of 2,145,660 entries. No matter how complicated of a search I throw at it, everything comes up within millionths of a second.bigdb.pngPrimarily with this databases I will be search latitude and longitude information to get County, State, and Region information. If I wanted all the locations within a broad area I would try something like this-Query

SELECT * FROM world WHERE latitude > '33.0224' AND latitude < '54.5323131523' AND longitude < '-96.294342' AND longitude > '-101.7.32144'

And the result-searchrk.pngNext I may also need to information based on ISP or Country information. Since there are only 3 countries in this table I will run this next query on the ISP field. Searching or ordering by strings is much different than searching or ordering by numbers, and here's why. Strings take much longer to search or sort compared to numbers because each character of the string is it's own number. And an algorithm to sort an array of strings would have to check each character against another to see which comes first. So ordering 2.1 millions rows by a string field quickly would usually be an issue. However...order.pngIf you want to compare this to a standard method try writing a script that generates 2million random strings, adds them to an array, and sorts them. See how long this takes.Indexing only works if an index is made every time a row is added or updated. When an index is made mysql must order the indexed field the old fashioned way (took 17-35 seconds for each field for me) so you wouldn't want to index a table that will undergo constant changes. Instead you would want to add temporary changes or rows to a table that updates the original every 5 minutes or so (ever noticed why youtube sometimes has 300 views on a video but 3000 comments?).To index a field with phpMyAdminenableindexing.pngAnyway, that's it. It makes me happy :)

Link to post
Share on other sites
Firstly, I have a table of 2,145,660 entries. No matter how complicated of a search I throw at it, everything comes up within millionths of a second.
Actually, that looks like thousands of a second. 0.0018s is 1.8 milliseconds or 1.8/1000 second.
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...