[colug-432] Bash fun

[Tom Hanlon]

Got it..

Thanks again.

That was fun, and in the end pretty straightforward.

Given a file that looks like this.

========
select city.name from world.city where id=12;
select city.name, country.name from world.city , world.country where  
city.countrycode=country.code;
Select * from world.country where code='AUS';
==========

You can prepend explain like this


bash_explain.sh
#!/bin/bash


while read line
do
echo EXPLAIN  "$line";

done

================

And then pipe to mysql , suppress column headers, and grab only the  
name of the table and the index used.

./bash_explain.sh <queries.txt | mysql -u root -s -ptraining world |  
cut -f 3,6

And the output looks like this.
Table_name index_name

city	PRIMARY
city	NULL
country	PRIMARY
country	PRIMARY

--
Tom

On 1 Nov 2009, at 15:37, Tom Hanlon wrote:

> Hey everybody,
>
> Thanks.
>
> Perhaps I was not completely clear, but I have made progress and
> should be able to solve this in the next round of rewrite.
>
> I want to take a file containing queries, append the word "explain" to
> each line , then capture the output, but only tab 3 and tab 6 of a tab
> delimited file.
>
> So a little more than batching mysql input.. but not all that much
> more..
>
> So basically read file, rewrite line | mysql | cut -f 3,6 >>file
>
> --
> Tom
> On 1 Nov 2009, at 15:14, Stephen P Potter wrote:
>
>> Tom Hanlon wrote:
>>> Thanks Matt,
>>>
>>> Good to know that eval is not needed.
>>>
>>> Another question, suppose I wanted to throttle this script.
>>>
>>> I want to run only 10 requests for second ?
>>>
>>> Part of the issue is this will spawn one mysql connection per line.
>>>
>>> The connection will be immediately dropped but we would still face:
>>> 	authentication, thread generation , execution , disconnect.
>>> 	authentication, thread generation , execution , disconnect.
>>> 	authentication, thread generation , execution , disconnect.
>>>
>>> Rather than.
>>> 	authentication, thread generation
>>> 		execution
>>> 		execution
>>> 		execution
>>> 		......
>>> 	disconnect
>>> 		
>>>
>>>
>>>
>>>
>>> Unfortunate but I do not know how to batch explain from inside  
>>> mysql.
>>>
>>> MySQL does take batch jobs as  source.sql > mysql >>outfile.txt
>>>
>>> So I might try and work it that way.
>>>
>>> Batch 100 lines, connect and run,
>>>
>>> Batch 100 lines, connect and run.
>>>
>>> If I find I have to run this on a production box.. well it would
>>> be  nice to not spawn one mysql connection per line.
>>>
>> You said your are taking all your input from a pre-existing file,
>> correct?  In that case, the batch nature of mysql means that each
>> query will be run sequentially in a single connection to the
>> database.  It doesn't make any significant different to batch 100 at
>> a time versus running them all sequentially (unless you really mean
>> batch; sleep; batch; sleep; batch).  Also, you want either "cat
>> source.sql | mysql" or "mysql < source.sql" not "source.sql >mysql".
>>
>> -spp
>
> _______________________________________________
> colug-432 mailing list
> colug-432 at colug.net
> http://lists.colug.net/mailman/listinfo/colug-432