Advance usage of the SELECT keyword
Previous Top Next

Advance usage of the SELECT keyword



Table1 includes only three song bouts produced by a bird named b109. In our database you will find () a table called b109clustered, which contains the entire song development of this bird. In this table you may not want to run the query shown above, because this query would return hundreds of thousands of records.
In this case, you should always using the keyword limit,  e.g., (Matlab):

[c1_duration, c1_pitch]=mysql('select duration, mean_pitch from b109clustered where duration>100 and duration<150 and mean_pitch>600 and mean_pitch<1100 limit 5000;');

will execute promptly and will return the first 5000 syllables that match your criteria. Because this table is already “clustered”, namely, the syllable types are already identified (see chapter xx) you could have simplified this query since this cluster is already identified as cluster number 4, so this query can turn to

select duration, mean_pitch from b109clustered where cluster=4 limit 5000;

Now, this query will return the first 5000 syllables of type 4, but what if you want not the first 5000 syllables by a random sample of 5000 syllables? That's easy:

select duration, mean_pitch from b109clustered where cluster=4 order by rand() limit 5000;

What if you only want to count how many syllables of each count exists in the table?
select count(*) from b109clustered where cluster=4;

will return 173581, so you can now repeat this query for clusters 0 (not clustered), 1 (introductory notes) and  3, 4 (song syllable types of this bird)  and tell that in this bird the frequency of types over development is:

0: 279354
1: 337884
3: 198997
4: 173581

 We can now see if those types are present in similar proportion throughout development. To do so, we will limit our queries to different dates.

select count(*) from b109clustered where month=8 and day<19 and cluster=4;

will return 0, but

select count(*) from b109clustered where month=8 and day=20 and cluster=4;

will return 858, telling us that (using our clustering method) this cluster was not identified before August 19. 

It should be now quite easy for you to realize how to select data by any combination of criteria. Using a simple for loop, you can pump the results of these queries into Matlab functions. When properly used, the MySQL server is a very potent device, capable of amazing performance returning complicated queries in a remarkable speed. 

  

Created using Helpmatic Pro HTML