Page 1 of 1
Which way is most efficient
Posted: Tue Jul 27, 2004 5:59 am
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?
Posted: Tue Jul 27, 2004 6:28 am
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
Posted: Tue Jul 27, 2004 8:12 am
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.
Posted: Tue Jul 27, 2004 8:15 am
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
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?
Posted: Tue Jul 27, 2004 8:16 am
by primate
Oops posting at the same time

Posted: Tue Jul 27, 2004 8:16 am
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
Mark
Posted: Tue Jul 27, 2004 9:28 am
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
Posted: Tue Jul 27, 2004 11:12 am
by hedge
Sounds to me like it should be a stored procedure.
Posted: Thu Jul 29, 2004 10:14 am
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!

) but try as I might MySQL won't sort out just the red and purple ones.....

Posted: Fri Aug 13, 2004 6:31 pm
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.