FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL optimisation





Fire Boar
Hi, I was wondering if anyone can point me in the right direction here. I've got a couple of SELECT statements, and they seem a little wasteful. Here's the first:

Code:
SELECT DISTINCT field1 FROM table WHERE field2='#' ORDER BY field1


field1 has 350 possibilities, but multiple results with the same field are very likely to be returned, even with very small queries (say 25 entries, 10 or more might have the same field1). field2 is a number between 1 and 6.

Here's the other one:

Code:
SELECT DISTINCT field1 FROM table WHERE field3 IN ('value1', 'value2', ...) ORDER BY field1


field1 is the same as before. field3 is a primary key integer with maximum length 6.

It seems a bit wasteful to select loads of entries then discard a good chunk of them via the DISTINCT clause. Is there a better way to achieve the same effect?
Faraz
Pardon me if I am wrong, but I don't think that DISTINCT keyword works like that. For example we have the following table:

field1 field2
a 1
b 1
c 2
d 2
e 3

and the following query:
Select field1 from table where field2=1.

Now the sql engine will go through the whole table and check which field2=1, and show only those records. I mean no matter what query you write, the engine goes through the whole table anyway (if you are using the while loop in your code, which I assume you are). It doesn't stop reading after the second record, because how would it know that there are no matching records after that?

Similarly with your query:
SELECT DISTINCT field1 FROM table WHERE field2='#' ORDER BY field1

Let's say first field1 is selected, and after that when it checks further fields, if they match the condition (field2=#), then the engine will compare them to the previous fields, and if any of previous fields=current field, then the engine ignores the current field, else it shows it in the results. So it doesn't select all the fields, and then check which are similar, and then discard them at the end. It does that as it reads the table.

I hope I am correct. Embarassed
AftershockVibe
Indeed. Because you are searching for a particular field2 (or field3) then all of the records you return are going to need to be individually compared anyway. Unique field1s is a very cheap process compared to this.

What you might want to consider doing is setting up indexes for the fields you mention. This will mean that the database stores a list of them in order (against the primary key). This is crucial because it means that searching for a specific value or range (e.g. '#') doesn't require looking through every record in the table. If they're ordered then the search algorithm knows which direction to progress in and when to stop based on a < or > comparison.
Fire Boar
Alright, I just read that DISTINCT was generally bad form, but I couldn't see any other way to achieve what I was looking for. Perhaps that was only referring to queries which use joins between several tables. As for indices, I've got them in strategic places across the table, so it should be fine.
AftershockVibe
Fire Boar wrote:
Alright, I just read that DISTINCT was generally bad form, but I couldn't see any other way to achieve what I was looking for. Perhaps that was only referring to queries which use joins between several tables. As for indices, I've got them in strategic places across the table, so it should be fine.


I've never seen this but I'd imagine it's because DISTINCT can be used to 'fix' the results of queries that have been badly designed or tables which have bad relational integrity (ie badly or not normalised). The way you are describing its use though seems fine to me.
Related topics
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
Do you use a CMS script?
Question about MySQL!
PHP, MySQL...
PHP Book (Php and Mysql for Dynamic Web Sites)
few mysql questions
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL
Build an online dictionary by PHP/MySQL
Make search engine With PHP and mySQL, for your site
PHP without mySQL
My Site not working
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.