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
extremely hard to know from this info, do you have an index set up?
guessing the 2 columns is id and number?
nope i tried but i couldn't work
yep
why not?
i don't know, it refreshed the page forever every time
🤔
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?
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
doesn't...tell me much
are they unique?
yeap
ok that's fine then
for a 50M row table it will take long to add the index
i guess so
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
think minutes or even an hour or longer for 50M records
yup
also, please tell me you're using pepared statements and not just dumping a PHP variable into a string?
Jochem, only one question at a time 
fair >_> If I need help with my prepared statements, I'll make a new topic ;P
english is not my main lang, i couldnt understand ðŸ˜
https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
I assume you're using
mysqli?yep not pdo
using query()
on localhost
right, then read that part of the manual. You need to use prepared statements to defend against SQL injection attacks
yeah i know there is risk but i'm not gonna share
they also improve performance a liiiitle bit
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 executemy files never gonna be public
i just wanna speed 😄
that's fine, just don't form bad habits 🙂
thanks for reminding 😄