tibberous Posted March 28, 2008 Share Posted March 28, 2008 I have a table with a couple million records that never changes. I am trying to speed up the time it takes to find a record. Is there anything I can do besides set an index? Does it help at all that the table never changes? Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/ Share on other sites More sharing options...
BillyJim Posted March 28, 2008 Share Posted March 28, 2008 Optimizing server and queries also perhaps? Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502800 Share on other sites More sharing options...
benjaminbeazy Posted March 28, 2008 Share Posted March 28, 2008 make sure you are only selecting pertinent data and use relational foreign keys for any and all duplicate data. Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502802 Share on other sites More sharing options...
cooldude832 Posted March 28, 2008 Share Posted March 28, 2008 can we see a query or is it a guarded secret? Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502808 Share on other sites More sharing options...
rofl90 Posted March 28, 2008 Share Posted March 28, 2008 Millions? Jeez, I hope for your sake thats account payments =] Only time I ever had millions of anything was when someone spammed me. Make sure you only select needed queries, no *'s to be honest, but don't expect too much, ex. PHPMyAdmin, very slow, but it has an excuse, likewise with you. Make sure your keys are correct, and you should be reasonably fast. Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502811 Share on other sites More sharing options...
cooldude832 Posted March 28, 2008 Share Posted March 28, 2008 Make sure you only select needed queries, no *'s to be honest, but don't expect too much, ex. PHPMyAdmin, very slow, but it has an excuse, likewise with you. Make sure your keys are correct, and you should be reasonably fast. PhpMyAdmin doesn't control your MySQL its a tool to aid in it. This is the tips I have to optimizing a query(s) 1) Reduce your number of queries to the least number that makes sense 1a) Do so by proper use of JOINs 2) Make queries as simple as possible 3) Make queries as specific as possible To achieve step 3 3a) Use Limits on large data sets and pagination to page results 3b) Use a very specific while loop 3c) Use group by/order by properly a another random tip I have found is CONCAT is a very powerful function. If you are going to take a url straight to an anchor tag use CONCAT and query it out like that. Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502817 Share on other sites More sharing options...
tibberous Posted March 28, 2008 Author Share Posted March 28, 2008 The table stores zip codes that are within a certain range of each other. Otherwise, the search would have to go through 43,000 zip codes, one at a time -- would be prohibitively slow. Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502825 Share on other sites More sharing options...
cooldude832 Posted March 28, 2008 Share Posted March 28, 2008 Is this done so you can return results within X miles of a zip code? if so there is a very fast way to do it with a well built mysql table Link to comment https://forums.phpfreaks.com/topic/98266-way-to-speed-up-queries-on-a-really-large-table/#findComment-502826 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.