Re^3: Need faster MySql data fetching with perl DBI

Replies are listed 'Best First'.
Re^4: Need faster MySql data fetching with perl DBI by shaolindoman (Acolyte) on Jul 16, 2004 at 03:25 UTC
Well I have narrowed it down to the SQL statements themselves. In the first script I call I tested my 2 SQL calls and they took a total of 21 seconds. No, I'm not familiar with indexes. I've started reading the O'reilly book Managing and Using MySql and I've seen it mentioned but I havn't taken the dive to learn about them yet. I have plenty of space to use (since I heard this is what they require). My database only takes up about 70 MB (down oddly enough from 175 MB since I switched from MS Access). Any suggestions for a quick start before I dive back into my book and the MySql Docs?	[reply]
Re^5: Need faster MySql data fetching with perl DBI by beable (Friar) on Jul 16, 2004 at 04:05 UTC
If you're not using indexes, that could well explain why your selects are slow. What you want to do is identify the columns you are selecting on, (like if you have `select * from mytable where name="bob"`), and index those columns. (In this case, index the "name" column). If you are doing joins, index the columns of both tables you are joining on. Refer to the MySQL manual for help: How MySQL uses indexes MySQL column indexes MySQL Create index syntax	[reply] [d/l]
Re^5: Need faster MySql data fetching with perl DBI by dragonchild (Archbishop) on Jul 16, 2004 at 12:59 UTC
To add to beable's answer, make sure that the columns you're joining together are the EXACT same datatype, down to the size. Otherwise, MySQL will make very expensive conversions, slowing your query down considerably. (In one of my tests, a 10second query didn't finish in 4 hours.) Always read the docs for something you plan on basing your application on. Why do people assume that complex applications like RDBMS's can simply be loaded up and they'll work perfectly the first time?!? ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply]


Syntactic Confectionery Delight
	PerlMonks