Need faster MySql data fetching with perl DBI

shaolindoman has asked for the wisdom of the Perl Monks concerning the following question:

Well, I decided to say the hec with our Access database and wrote scripts all day long to transfer everything over to Mysql. (Now I don't have to worry about using a MS server woohoo!). However, I've ran into a little problem. I have 5 tables of data and depending on user input I search different tables. I've found that if the data is in one or all of the searched tables the search is fast. However, when if even one of the tables being searched doesn't contain the item I need, it is slow. Very very very slow. It goes from taking 2 to 3 seconds to taking about 15 to sometimes 25 seconds. I'm dealing with around 350,000 items in the database. My question is: Is this a perl problem that could some how be circumvented, or a MySql problem (which i assume it is). The only reason I doubt it might be a MySql problem is when I do a command line MySql search and the item isn;t there it takes at most 3 to 5 seconds. Here is some sample code listed below. Anyone have ideas?


my $dbh = DBI->connect("DBI:mysql:database=mydatabase;host=localhost",
                         "name", "password",
                         {'RaiseError' => 1});

$SQLStatement = "SELECT etc..."            
$sel = $dbh->prepare( $SQLStatement ); 
$sel->execute() || die "Could not open database";
while( ($somedata) = $sel->fetchrow_array  )
{#actions...}
[download]

Comment on Need faster MySql data fetching with perl DBI Download Code

Replies are listed 'Best First'.
Re: Need faster MySql data fetching with perl DBI by blokhead (Monsignor) on Jul 16, 2004 at 01:43 UTC
Are you absolutely sure that it's the same SQL statement that produces such great differences between DBI and the command-line client? Not that a Perl-caused slowdown is out of the question, but that's always the first thing I'd double-check. If you're getting back a ton of data from each query, then a slowdown in Perl is quite likely, especially if you're using `fetchrow_array`. Take a look at Speeding up the DBI, which has recipes for faster ways to fetch lots of data. However, it sounds like the slowdown happens even when no data is returned. In that case, you should do some careful DBI profiling. Most likely most of the time is spent waiting for the MySQL server's slow response.. blokhead	[reply]
Re^2: Need faster MySql data fetching with perl DBI by shaolindoman (Acolyte) on Jul 16, 2004 at 02:07 UTC
Ok, well I took one statement DIRECTLY out of my code and tried it on the command line. Sure enoug, 6.98 seconds. That seems to be the slow down now I assume. I guess I'm going to have to figure out more efficient ways to fetch the data. I will take a look at the links you posted to see if they provide any help. Thanks!	[reply]
Re^3: Need faster MySql data fetching with perl DBI by beable (Friar) on Jul 16, 2004 at 02:56 UTC
Did you use indexes?	[reply]
Re^4: Need faster MySql data fetching with perl DBI by shaolindoman (Acolyte) on Jul 16, 2004 at 03:25 UTC
Re^5: Need faster MySql data fetching with perl DBI by beable (Friar) on Jul 16, 2004 at 04:05 UTC
Re^5: Need faster MySql data fetching with perl DBI by dragonchild (Archbishop) on Jul 16, 2004 at 12:59 UTC
Re: Need faster MySql data fetching with perl DBI by ercparker (Hermit) on Jul 16, 2004 at 04:34 UTC
if you have a where clause in your select then you should try using an index if you're not. Once you have the index setup you can check with explain to make sure the select is using the correct index.	[reply]
Re: Need faster MySql data fetching with perl DBI by dws (Chancellor) on Jul 16, 2004 at 05:54 UTC
I've found that if the data is in one or all of the searched tables the search is fast. However, when if even one of the tables being searched doesn't contain the item I need, it is slow. You seem to be contradicting yourself. If you search for data in two tables, don't find it in the first, but find it in the second, is the search fast, or is it slow? What is the nature of the data you are searching for (e.g., is it keywords, part numbers, or something like that), and do you have that field indexed? If not, then you're doing a complete table scan for each search, which is a very poor approach to doing searches over a large search space.	[reply]
Re^2: Need faster MySql data fetching with perl DBI by shaolindoman (Acolyte) on Jul 16, 2004 at 14:38 UTC
Well you all, I must thank you once again. I stayed up reading about indexes and this morning I implemented them for all of my `WHERE` clauses. Wouldn't you know, that 20 to 25 second search I was talking about, well it is down to basically nothing now. It's instant on the website. Thanks so much for all the help.	[reply] [d/l]
Re^3: Need faster MySql data fetching with perl DBI by beable (Friar) on Jul 17, 2004 at 00:10 UTC
Now you better read up on MySQL security. If you didn't know about indexes, then chances are that you don't know about database security either.	[reply]