MySQL

This is a post from my old blog which i felt was worthy of reposting...

I have to admit MySQL is a great bit of kit, and the apachefriends have done a great job of encorporating into lampp/xampp! However I do a have a few small gripes which I suspect can be easily resolved!

1) LIMIT is documented as being optimised however appears to be quite the opposite. Consider the following example...

Table: Companies, Records: 3,000,000

SELECT *FROM CompaniesLIMIT 0, 1000;
Exec time: 0.1s
SELECT *FROM CompaniesLIMIT 1000000, 1000;
Exec time: 60.5s

I'm guessing the entire table is being read until the records specified have been passed at which point the result is returned. If however we try;

SELECT *FROM CompaniesWHERE id > 1000000AND id <>
Exec time: 0.1s


2) NESTED Queries don't seem to function as expected. I expect this one if me just doing it wrong!

SELECT match_group FROM Companies ORDER BY RAND()LIMIT 0, 1;

Returns a random match_group- fine!

SELECT *FROM CompaniesWHERE matchgroup = X;

Returns all companies with match_group X- fine! However when I try;

SELECT *FROM CompaniesWHERE matchgroup = ( SELECT match_group FROM Companies ORDER BY RAND() LIMIT 0, 1;);

MySQL seems to spiral out of control- i have to kill the process as the cpu runs @ 100%

I attempted using MySQL vars as an alternative and failed, however Bob Field has now helped me get this working (thanks Bob!);

SELECT match_groupINTO @aFROM pd_companyGROUP BY match_groupORDER BY RAND( )LIMIT 0 , 1;
SELECT *FROM pd_companyWHERE match_group = @a;


L

Read more!

Hello!

A very quick one, just to say hi. I am an IT Consultant in London (UK) during the day and a photographer in clubs during the night. I started a blog a while back but spammers got the better of it! So here am trying to give it another shot...

I will try and dig up a few of my old posts, but you can mostly expect to see me blogging about;

• my favourite photos
• hardware/software issues that have/are really bugging me (often followed very shortly by solutions)
• a little general chat

L

Read more!