PHP Mysql speed

i have a database which have 2 columns but about 50M row, when i use only one where statment it will take about 12sec, how can i speed it up? my code like this SELECT id FROM tablename WHERE number=$number
26 Replies
ErickO
ErickO•16mo ago
extremely hard to know from this info, do you have an index set up? guessing the 2 columns is id and number?
zimcim
zimcim•16mo ago
nope i tried but i couldn't work yep
ErickO
ErickO•16mo ago
why not?
zimcim
zimcim•16mo ago
i don't know, it refreshed the page forever every time
ErickO
ErickO•16mo ago
🤔 the page? also, what is this number column? what does it contain are we talking numbers from 1 to 10, phone numbers, ids from other sites?
zimcim
zimcim•16mo ago
i'm using codes on my index.php i tried there and phpmyadmin, both loading forever not from other sites but they are like spesific numbers for users but not like id
ErickO
ErickO•16mo ago
doesn't...tell me much are they unique?
zimcim
zimcim•16mo ago
yeap
ErickO
ErickO•16mo ago
ok that's fine then for a 50M row table it will take long to add the index
zimcim
zimcim•16mo ago
i guess so
ErickO
ErickO•16mo ago
keep in mind it's literally recreating the entire table as a sorted tree, so don't despair that will be your solution, can't do much else
Jochem
Jochem•16mo ago
think minutes or even an hour or longer for 50M records
ErickO
ErickO•16mo ago
yup
Jochem
Jochem•16mo ago
also, please tell me you're using pepared statements and not just dumping a PHP variable into a string?
ErickO
ErickO•16mo ago
PepeLaugh Jochem, only one question at a time kappa
Jochem
Jochem•16mo ago
fair >_> If I need help with my prepared statements, I'll make a new topic ;P
zimcim
zimcim•16mo ago
english is not my main lang, i couldnt understand 😭
zimcim
zimcim•16mo ago
yep not pdo using query() on localhost
Jochem
Jochem•16mo ago
right, then read that part of the manual. You need to use prepared statements to defend against SQL injection attacks
zimcim
zimcim•16mo ago
yeah i know there is risk but i'm not gonna share
ErickO
ErickO•16mo ago
they also improve performance a liiiitle bit
Jochem
Jochem•16mo ago
query is fine as long as you can be 100% sure there is no user submitted data in the query, otherwise you should use prepared statements with prepare and execute
zimcim
zimcim•16mo ago
my files never gonna be public i just wanna speed 😄
Jochem
Jochem•16mo ago
that's fine, just don't form bad habits 🙂
zimcim
zimcim•16mo ago
thanks for reminding 😄
Want results from more Discord servers?
Add your server