Hi, could anyone help me please. im optimizing my query to make it faster.. i did all the necessary requirements, including using indexes. here is my query. Im joining two tables here, i have more than 15K of rows on my table SELECT a.fullname, b.address1, b.city, b.state, b.zip, b.address2, b.unit_number FROM referrals AS a INNER JOIN address AS b ON a.clientid = b.clientid WHERE a.clientid=b.clientid This query is too slow to return results. it would take more than 150s to return all results how can i make this faster? thank you
INNER JOIN address AS b ON a.clientid = b.clientid WHERE a.clientid=b.clientid This is redundant, otherwise this is as optimized as it can get. SELECT a.fullname, b.address1, b.city, b.state, b.zip, b.address2, b.unit_number FROM referrals AS a INNER JOIN address AS b ON a.clientid = b.clientid The WHERE a.clientid=b.clientid is unnecessary since the relationship is already being declared in the INNER JOIN clause. 2 questions. First, do you have to select all of the rows in the table, or could you use LIMIT and only select the rows that you need? Second, is there an index on these columns, or a foreign key relationship between the tables on the clientid columns? These are really the only things that could improve this from a database structure or query standpoint. Any other optimization is going to have to occur in the my.ini/my.cnf file or through hardware changes.
Actually, you can use the explain statement in front of your query to see if its using keys or not. Chances are you have not defined your indexes yet.