Which way is most efficient

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Which way is most efficient

Post by primate »

Hi,

I am writing a script that will potentially be parsing through a database containing up to 10,000 records per day. So if I am looking at a period of a month, which is possible, thats 300,000 or so records.

The script will be pulling out records based on matching selected portions of text within those records.

Is it more efficient to use LIKE in my SQL query to reduce the amount of records being passed to php to a minimum, or should I pass all the records between the selected dates over to php and let php's string matching functions chew through it? :)

Certainly the script outputs the results faster when I use a more elaborate SQL query to reduce the number of results being passed to php, but what is the normal approach to this situation?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

The best way would be to use a more advanced Query and only select the data you actually need.

Put it this way, say you have a bag of skittles. You want all the red ones.

You can either ask for all the red ones - job done.

or...

You can ask for all the skittles, and sort through them yourself one by one.

Which is more efficient!?

Mark
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Occasionally it can be faster to do some work in php rather than doing it all in sql. It all depends on the details of the query & the database. With very complex queries the pure sql method might not always be optimal. However, it is a good rule of thumb to try this first. Experiment & test to find the best approach.
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

I hear what you say but at some point someone still has to sort the red skittles, unless of course you prefer purple ones :wink:

In my instance php and SQL is sitting on the same server on my corporate LAN. The SQL server is doing other things too, as is php, so I want to know which method will impact the performance of the server the least.

So my question is really, all things being equal, is SQL or php better at matching a text pattern in a text string? - can I infer from your answer that its SQL?
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Oops posting at the same time :wink:
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

McGruff wrote:Occasionally it can be faster to do some work in php rather than doing it all in sql. It all depends on the details of the query & the database. With very complex queries the pure sql method might not always be optimal. However, it is a good rule of thumb to try this first. Experiment & test to find the best approach.
...or use skittles :lol:

Mark
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

McGruff wrote:Experiment & test to find the best approach.
Sounds like the best approach even if you use skittles.

How MySQL uses indexes might interest you?
MySQL Presentations: Optimizing MySQL wrote: When MySQL uses indexes

Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.

SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;

SELECT * FROM table_name WHERE key_part1 IS NULL;

When you use a LIKE that doesn't start with a wildcard.
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

Retrieving rows from other tables when performing joins.
SELECT * from t1,t2 where t1.col=t2.key_part

Find the MAX() or MIN() value for a specific index.
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

ORDER BY or GROUP BY on a prefix of a key.
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

When all columns used in the query are part of one key.
SELECT key_part3 FROM table_name WHERE key_part1=1

--------------------------------------------------------------------------------

When MySQL doesn't use an index

Indexes are NOT used if MySQL can calculate that it will probably be
faster to scan the whole table. For example if key_part1 is evenly
distributed between 1 and 100, it's not good to use an index in the
following query:

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

If you are using HEAP tables and you don't search on all key parts with =

When you use ORDER BY on a HEAP table

If you are not using the first key part
SELECT * FROM table_name WHERE key_part2=1

If you are using LIKE that starts with a wildcard
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'

When you search on one index and do an ORDER BY on another
SELECT * from table_name WHERE key_part1 = # ORDER BY key2

Read More about Optimizing MySQL Apps
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

Sounds to me like it should be a stored procedure.
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

Bech100 wrote:The best way would be to use a more advanced Query and only select the data you actually need.

Put it this way, say you have a bag of skittles. You want all the red ones.

You can either ask for all the red ones - job done.

or...

You can ask for all the skittles, and sort through them yourself one by one.

Which is more efficient!?

Mark
I've been watching this post since it started, and as a result am now sitting here with my bag of Skittles (two big bags for the price of one at Tescos! :D ) but try as I might MySQL won't sort out just the red and purple ones..... :( :wink:
Serberus
Forum Newbie
Posts: 5
Joined: Fri Aug 13, 2004 6:31 pm
Location: Hertfordshire, UK

Post by Serberus »

Depending on the version of MySQL you're using you can use MATCH AGAINST syntax, which is reportedly 1000 quicker than like. This is only available on MySQL 4.1 and above I believe though. You must index the text column too to use this.
Post Reply